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

    Dynamic date if statement in Qlik Sense set analysis

    David Lin

      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.


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

          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