4 Replies Latest reply: Feb 27, 2017 9:48 AM by Lukas Amann RSS

    Set Analysis: Leap Year in previous period comparison

    Lukas Amann

      Hello Community,

       

      Iam looking for a best practice solution on my problem:

      We still have a lot of old Dashboards which still uses IF functions for calculation (dark times before set Analysis) . Ill upgrade them from time to time and replace the old Expression with new ones.

       

      So there is a Expression which shows the sales from the  Previous Year --> From Date - To Date.

      The Dates are stored in Variables which  the user can manipulate via Inputbox.

       

      The Expression looks like this:

      Sum ( IF(Date>= addMonths(From_Date,-12) AND Date<=  IF(num(floor(To_Date))= num(floor(MonthEnd(To_Date))), Monthend(addMonths(To_Date,-12)), addMonths(To_Date,-12)),(Sales))) 

       

      At First i thought i can replace them with using :

       

      =sum({$<Date={">=$(=addMonths(From_Date,-12))<=$(=addMonths(To_Date,-12))"} >} Sales)


      But i forgot about the leap Year. So if you compare the date 01.02.2017 to  28.02.2017 with the Previous Year. it should compare it with the end date of 29.02.2016 since its the last day in Februar 2016.

      What would be a good solution in Set Analysis?

       

      greetz Lukas