Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Dynamic date if statement in Qlik Sense set analysis

Hi all,

I am trying to turn what i have below into a more dynamic set analysis for the latest 5 years. basically what this does is to sum up all cost for each FY in a line chart. any suggestions?

if ([FY Occurred]='FY 2012-2013',rangesum(above(total sum({$<[FY Occurred]={'FY 2012-2013'}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]='FY 2013-2014',rangesum(above(total sum({$<[FY Occurred]={'FY 2013-2014'}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]='FY 2014-2015',rangesum(above(total sum({$<[FY Occurred]={'FY 2014-2015'}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]='FY 2015-2016',rangesum(above(total sum({$<[FY Occurred]={'FY 2015-2016'}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]='FY 2016-2017',rangesum(above(total sum({$<[FY Occurred]={'FY 2016-2017'}>}[Amount Paid]),0,rowno(total)))

)))))

i have got as close as below, but its not getting me the same as above:

IF ((Year(YearEnd($(vWarehouseLoadDate), 0, 7))- MID([Financial Year (Date Injury Occurred)],9,4))<5,

dual (rangesum(above(total sum({$<[Financial Year (Date Injury Occurred)]={$(='FY '&MID([Financial Year (Date Injury Occurred)],4,4)&'/'&MID([Financial Year (Date Injury Occurred)],9,4))}>}[Amount Paid]),0,rowno(total))) ,

MID([Financial Year (Date Injury Occurred)],9,4)))

note: vWarehouseLoadDate=2017

[Financial Year (Date Injury Occurred)] = FY 2012/2013  FY 2013/2014 ...etc.


Tags (1)
1 Solution

Accepted Solutions
rahulpawarb
Not applicable

Re: Dynamic date if statement in Qlik Sense set analysis

Hello David,

Trust that you are doing good!

Please refer given sample expression:

if([FY Occurred]= 'FY ' & (Year(Today())-5) & '-' & (Year(Today())-4), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-4) & '-' & (Year(Today())-3), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-4) & '-' & (Year(Today())-3))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-3) & '-' & (Year(Today())-2), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-3) & '-' & (Year(Today())-2))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-2) & '-' & (Year(Today())-1), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-2) & '-' & (Year(Today())-1))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-1) & '-' & (Year(Today())),   rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-1) & '-' & (Year(Today())))"}>}[Amount Paid]),0,rowno(total)))

)))))

Hope this will be helpful.

Regards!

Rahul

3 Replies
rahulpawarb
Not applicable

Re: Dynamic date if statement in Qlik Sense set analysis

Hello David,

Trust that you are doing good!

Please refer given sample expression:

if([FY Occurred]= 'FY ' & (Year(Today())-5) & '-' & (Year(Today())-4), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-4) & '-' & (Year(Today())-3), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-4) & '-' & (Year(Today())-3))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-3) & '-' & (Year(Today())-2), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-3) & '-' & (Year(Today())-2))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-2) & '-' & (Year(Today())-1), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-2) & '-' & (Year(Today())-1))"}>}[Amount Paid]),0,rowno(total))),

if([FY Occurred]= 'FY ' & (Year(Today())-1) & '-' & (Year(Today())),   rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-1) & '-' & (Year(Today())))"}>}[Amount Paid]),0,rowno(total)))

)))))

Hope this will be helpful.

Regards!

Rahul

Not applicable

Re: Dynamic date if statement in Qlik Sense set analysis

thank you for your suggestion, Rahul.

this section here does not evaluate: {'= $(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))'}>}


worked after i changed to below:


{ "$(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))"}>}

rahulpawarb
Not applicable

Re: Dynamic date if statement in Qlik Sense set analysis

Hello David,

Extremely Sorry for putting erroneous expression (corrected my earlier response accordingly).

Glad that post modification it worked for you.

Regards!

Rahul