Getting previous value against a category and subcategory

[Anonymous]

Hey guys,

Got struck in one task wherein i want to fetch previous fiscal year month end value against each Finance LOB and Claim Type.

For ex:- Finance LOB = MOTOR-2W and Claim Type = OD and as mentioned in the attached excel Retension Loss for FY 2016 and Month =12 (March) is 100 and that value 100 should get applied on each month in the next fiscal year i.e FY 2017 Apr till FY 2017 Mar.

Similarly for FY 2018 Apr - FY 2018 Mar Fiscal year 2017 value of 200 should get applied in FY 2018 Apr till FY 2018 Mar.

Expected Output is also specified in the attached excel.

I tried using peek and previous function but not able to get the value against each Finance LOB and Claim Type i.e OD or TP

Thanks & advance,

Girish Chhatani

[olivierrobin]

hello

i would use

1 - load your data

2 - left join the table on itself

left join(table)

load ClaimType,

Claim_Fiscal_Year+1 as Claim_Fiscal_Year,

FINANCE_LOB,

RETENTION_LOSS as ExpectedOutput

resident MyTable

where Fiscal_MonthNo=12

[Anonymous]

Tried using but didn't worked.

Thanks & Regards,

Girish Chhatani

[olivierrobin · Accepted solution]

I Tried that :

t:
LOAD ClaimType,
Claim_Fiscal_Year,
Fiscal_Month_No,
FINANCE_LOB,
RETENTION_LOSS,
[Expected Output]
FROM

(biff, embedded labels, table is Sheet_final$);

left join(t)

load ClaimType,

Claim_Fiscal_Year+1 as Claim_Fiscal_Year,

FINANCE_LOB,

RETENTION_LOSS as ExpectedOutput2

resident t

where Fiscal_Month_No=12
;
and the resilt is

that seems to be your requirement

[Anonymous]

Thank you Olivier actually i had mistakenly added month no in join in the previous script.

Thanks its working fine.

[olivierrobin]

glad to help you

tag this answer as correct and close the thread