Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

luke1986
New Contributor III

Set Analysis: Leap Year in previous period comparison

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

4 Replies
MVP
MVP

Re: Set Analysis: Leap Year in previous period comparison

May be this:

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

In addition, I would also use Date() function to make sure that date format matches

=Sum({$<Date={">=$(=Date(MonthStart(From_Date, -12), 'DD.MM.YYYY'))<=$(=Date(MonthEnd(To_Date,-12), 'DD.MM.YYYY'))"} >} Sales)

luke1986
New Contributor III

Re: Set Analysis: Leap Year in previous period comparison

Hi Sunny,

Well that locks the end Date to End of Month, but it should still be editable. So for example if you select the 27.02.2017 it should output the 27.02.2016 in set analysis. Only if you Select the last in Month, it should look up the last day in Previous Year.


Greetz Lukas

MVP
MVP

Re: Set Analysis: Leap Year in previous period comparison

But that seems somewhat strange, if you compare 27th, you compare 27 days, but if you select 28th, the comparison is for 29 days. On the other hand, what will happen you have 29th selected and you will compare it with 28th and when 28th selected, it will again compare to 28th?

luke1986
New Contributor III

Re: Set Analysis: Leap Year in previous period comparison

I agree with you, its some kind of strange point of view. You will never be able to look back exactly 28 days in this scenario. But its what the customer always had, i thought if there is an easy solution ill just use it. Maybe ill just talk to them and try to change their minds, since it doesnt make any sense to me neither.  Thanks anyway!