Leap year Problem

[#1]

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.

[Miguel_Angel_Baeyens]

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)

[#3]

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.

Thanks,

Swetha

[Miguel_Angel_Baeyens]

Hello Swetha,

I'm afraid I'm missing something. Obviously when comparing to a leap year, you have one day longer your calendar, and it would not be correct to just ignore that day, as it will mislead your reports and graphs.

That's why I told you about AddMonths() function, which returns the number of months you set. I understand that "gap" in leap years, but in my opinion that is what is has to be.

Anyway, if you just want to jump over 2/29/2008 you can do it in your load script with a map of possible leap februaries and their correspondences for previous year. I think I should check your file to address your issue correctly.

[#5]

The Order_date_key column is not a date(its just a number-1234,45678,.....) in my data so addmonth is not working here I think.I am trying to attach the file but the site is not allowing me to do so.

Thanks,

Swetha

[#6]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/5758.Doc1.zip:550:0]

[#7]

Pls find the enclosed doc which I was trying to explain and pls suggest solution if possible.

Thanks,

Swetha

[Miguel_Angel_Baeyens]

Hello Swetha,

The only way I know, now I've seen your data, to control that is checking conditionally that on feb 29 2008 and greater until dec 31 2008 you should sum

Date(AddMonths(Date, -12) + 1)

otherwise

AddMonths(Date, -12)

. I don't know any other smarter or clearer solution, unfortunately.