Year-on-year comparison Edit expression

[Sebastian_Dec]

Hi, I want to comparison year-on-year but not on Today() function but on specific user selection.

I load data (364 is days between 2022 vs 2023):

LOAD
date(hid_data) as Data,
date(hid_data - 364) as Data_LY,
sales

And in pivot table I want  to have comparison sales:

Selected date (its ok):

Sum({<Data = Data>} sales)

Previous date is not correct and I have error, can anyone help me what is bad? 😕

Sum({<Data = Data_LY>} sales)

I have "0", and should have values, when I write:

Sum({<Data = {'27.01.2022'}>} sales)
Sum({<Data = {'26.01.2023'}>} sales)

its ok.

[md_talib]

variables can be your solution.

2 for  Current Year 

vCalMinDate= Date(Min(Posting_Date),'DD/MM/YYYY')

vCalMaxDate=Date(Max(Posting_Date) ,'DD/MM/YYYY')

2 for Previous year 

vPYCalMinDate=Date(AddYears(Min(Posting_Date),-1),'DD/MM/YYYY') 

vPYCalMaxDate=Date(AddYears(Max(Posting_Date),-1),'DD/MM/YYYY')

[Sebastian_Dec · Accepted solution]

Ok, i use another method, a load calendar with dates and difference on days, and mark Date (Data) as Data-DifferenceDays.

Sum({<Data={"$(=Date(Max(Data)-DifferenceDays))"}>} sales)

I'm a bit surprised that I had to mess around like that, and Qlik doesn't have a similar option enabled by default...

[Sebastian_Dec]

@md_talib your suggestions were close, I can make variables in Lead editor like this:

LET Difference = 364;

And then on Edit expression:

Sum({<Data={"$(=Date(Max(Data)-$(Difference)))"}>} sales)

But its ok on 2022/2023, in other years the Difference could be different, especially in a leap year or some movable holidays if we have some other shifts in the calendar, in such an event loading the calendar for +20 years may be faster and safer.

This is just my opinion, maybe someone with more experience will know better...