Forum Discussion
How Create the calculated Colum to identify Part-payment
Hi All,
I need your help to Create the calculated column “Part-payment Status” based on the condition if any ref order exists in the previous financial year and current financial year data till the last quarter (FY 21 –Q1 to FY-22 Q1) then it should be marked as “Part-payment”.
Sharing below sample data along with desired output.
Current financial year FY 22 –Q1 to FY22 –Q2
Previous financial year FY-21 Q1 to FY-21 Q4
current financial year last quarter: FY-22 Q1
Ref No_ Period Status (desired results)
NG/19-20/8066 FY-21 Q1 NO
NK/19-20/099246 FY-21 Q1 NO
NK/19-20/109582 FY-21 Q1 NO
NG/19-20/8066 FY-21 Q2 Partpayment
NK/19-20/139104 FY-21 Q2 NO
NK/19-20/143041 FY-21 Q3 NO
NK/19-20/099246 FY-21 Q3 Partpayment
NK/19-20/145050 FY-21 Q4 NO
NK/19-20/145800 FY-21 Q4 NO
NK/19-20/109582 FY-21 Q4 Partpayment
NK/19-20/146240 FY-21 Q4 NO
NK/19-20/146256 FY-22 Q1 NO
NK/19-20/146501 FY-22 Q1 NO
NG/19-20/8066 FY-22 Q1 Partpayment
NK/20-21/000160 FY-22 Q1 NO
NK/20-21/000203 FY-22 Q1 NO
NK/19-20/146256 FY-22 Q1 Partpayment
NK/20-21/000228 FY-22 Q1 NO
NK/19-20/099246 FY-22 Q1 Partpayment
Anonymous
I am still not very clear about this logic. However, i can get the expected output. I am not sure if this solution works for your real data or not. please let me know if anything else need to be modified.
5 Replies
Anonymous , Create these 2 columns
Rank Period = rankx(Table, [Period],,asc,dense)Status =
var _cnt = countx(filter(Table, [Ref No_] = earlier([Ref No_]) && [Rank Period] = earlier([Rank Period])-1),[Period])
return
if( isblank(_cnt) || _cnt =0, "No", "Partpayment")Hi amitchandak ,
Thanks for reply
I am getting the below error seems I am doing something wrong.
Please help me to figure out what I doing wrong and it would be very helpful if can share the working file with me
Thanks
Lavdeep
Anonymous
not clear about your question. What is the last quarter? why some payment in Q1 some in FY21 Q4?
What's your fiscal year start time and end time?
Hi ryan_mayu,
This is just sample data let me try to explain with example
Based on sample data
Current financial year FY 22 –Q1 to FY22 –Q2
Previous financial year FY-21 Q1 to FY-21 Q4
current financial year last quarter : FY-22 Q1
Example This ref order “NK/19-20/099246” we need to check in FY-21 Q1 to FY-22 Q1 if ref order found between this data range than is should be mark as “Partpayment”
Refer sample data desired results fields this “NK/19-20/099246” exists in “FY-21 Q1” and FY-21 Q3 hence I mentioned Partpayment
This means refer order created earlier now we are getting Part-payment
I hope this help to understand my question
Anonymous
I am still not very clear about this logic. However, i can get the expected output. I am not sure if this solution works for your real data or not. please let me know if anything else need to be modified.
