topic Re: Set Analysis: Leap Year in previous period comparison in QlikView

Set Analysis: Leap Year in previous period comparison

luke1986 — Mon, 27 Feb 2017 14:06:36 GMT

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

Re: Set Analysis: Leap Year in previous period comparison

sunny_talwar — Mon, 27 Feb 2017 14:14:31 GMT

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)

Re: Set Analysis: Leap Year in previous period comparison

luke1986 — Mon, 27 Feb 2017 14:22:05 GMT

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

Re: Set Analysis: Leap Year in previous period comparison

sunny_talwar — Mon, 27 Feb 2017 14:32:40 GMT

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?

Re: Set Analysis: Leap Year in previous period comparison

luke1986 — Mon, 27 Feb 2017 14:48:28 GMT

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!