topic Leap year Problem in QlikView

Leap year Problem

Wed, 03 Mar 2010 15:07:31 GMT

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 — Wed, 03 Mar 2010 15:46:41 GMT

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

Wed, 03 Mar 2010 16:37:11 GMT

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

Leap year Problem

Miguel_Angel_Baeyens — Wed, 03 Mar 2010 16:48:18 GMT

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.

Leap year Problem

Wed, 03 Mar 2010 17:46:52 GMT

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

Leap year Problem

Wed, 03 Mar 2010 19:05:48 GMT

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

Leap year Problem

Wed, 03 Mar 2010 19:08:42 GMT

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

Thanks,

Swetha

Leap year Problem

Miguel_Angel_Baeyens — Thu, 04 Mar 2010 15:18:45 GMT

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.