Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
girishchhatani1
New Contributor III

Getting previous value against a category and subcategory

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

1 Solution

Accepted Solutions
olivierrobin
Valued Contributor II

Re: Getting previous value against a category and subcategory

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

View solution in original post

5 Replies
olivierrobin
Valued Contributor II

Re: Getting previous value against a category and subcategory

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

girishchhatani1
New Contributor III

Re: Getting previous value against a category and subcategory

Tried using but didn't worked.

Thanks & Regards,

Girish Chhatani

olivierrobin
Valued Contributor II

Re: Getting previous value against a category and subcategory

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

View solution in original post

girishchhatani1
New Contributor III

Re: Getting previous value against a category and subcategory

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

Thanks its working fine.

olivierrobin
Valued Contributor II

Re: Getting previous value against a category and subcategory

glad to help you

tag this answer as correct and close the thread