# Qlik Sense App Development

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

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

Tags (1)
1 Solution

Accepted Solutions
Not applicable

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

3 Replies
Not applicable

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

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

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

Community Browser