Dynamic date if statement in Qlik Sense set analysis

[#1]

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.

[rahulpawarb · Accepted solution]

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]

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]

Hello David,

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

Glad that post modification it worked for you.

Regards!

Rahul