How Create the calculated Colum to identify Part-payment

[lavdeepk]

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

[ryan_mayu]

@lavdeepk 

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?

[lavdeepk]

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

[ryan_mayu · Accepted solution]

@lavdeepk 

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.

[amitchandak]

@lavdeepk , 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")

[lavdeepk]

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