7 Replies Latest reply: Mar 4, 2010 10:18 AM by Miguel Angel Baeyens de Arce RSS

    Leap year Problem

      Hi All,

      I am using the below formulas to calculate previous year and current year sales:

      Current_Year:sum({$<Order_Date_Key={"<=$(=(max(Order_Date_Key)))"}, status={"Current"}>} Sold_At*Qty)

      Previous_Year:if(year(Order_CALENDAR_DATE)='2008',sum({$<Order_Date_Key={"<=$(=max(Order_Date_Key)-366)"}, status={"Previous"}>} Sold_At*Qty),
      if(date(Order_CALENDAR_DATE,'M/D/YYYY')='2/29/2008','-',sum({$<Order_Date_Key={"<=$(=max(Order_Date_Key)-366)"}, status={"Previous"}>} Sold_At*Qty)))

      The above previous year adjustment is done to address when the year is leap year but its not working.Any help would be appreciated.

        • Leap year Problem
          Miguel Angel Baeyens de Arce

          Hello Swetha,

          I'd use (untested)

          AddMonths(Max(OrderDateKey), -12)
          to generate an actual 12 months backwards expression. You can use it as well in set analysis and use only one expression
          sum({$<Order_Date_Key={"<=$(=AddMonths(max(Order_Date_Key), -12))"}, status={"Previous"}>} Sold_At*Qty)

            • Leap year Problem

              Thanks Miguel.

              I have tried your options but am unable to acheive the needed.Let me explain my problem clearly.I have 4 columns:

              Column1:Curr_Order_Date==date(Order_CALENDAR_DATE) which gives 2/1/2008...........2/29/2008 if I select Feb 2008

              column2:sum({$<Order_Date_Key={"<=$(=(max(Order_Date_Key)))"}, status={"Current"}>} Sold_At*Qty) which gives the current year sum of sold_at*qty

              Column3:sum({$<Order_Date_Key={"<=$(=max(Order_Date_Key)-365)"}, status={"Previous"}>} Sold_At*Qty) which gives sum of 2007 (as 2007 is previous year for 2008.

              Column4: date(Order_CALENDAR_DATE-365,'M/D/YYYY') which gives 2/1/2007 to 3/1/2007 as 2/29/2007 is not there.

              I mean whenever a leap year is coming my totals for the previous year are wrong as in a leap year in the month of february there are 29 days so it is substituting 3/1/2007 for 2/29/2008.Hope I am clear.


              The site is not allowing to attach a doc.