3 Replies Latest reply: Mar 1, 2017 7:34 AM by Rahul Pawar

# 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)))

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

• ###### Re: Dynamic date if statement in Qlik Sense set analysis

Hello David,

Trust that you are doing good!

```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)))
)))))

```

Regards!

Rahul

• ###### 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))"}>}

• ###### 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