Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wanyunyang
Creator III
Creator III

master calendar doesn't work

Hi guys,

I have a master_calendar table with Year, Month, Date, and LINK_DATE (Date in master_calendar table) which links to sales table by LINK_DATE (sales_date in sales table). Now I'm trying to calculate Previous Year's sales amount. My function is:

Sum({<Date={">=$(=addyears(Min(Date),-1))<=$(=addyears(Max(Date),-1))"}>}[SalesAmount])


I have filters: Year, Month, Date, Date as Date Range Picker. When I filter with month or date or date range picker, it works. But when I filter with year, it only shows me 0.

I'm wondering what's wrong. Any advice helps. Thanks in advance!

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

What I see missing in this expression is you haven't ignored the selected fields of master cal.

Meaning if you have selected Year then it will calculate the expression correctly, but by default the Identifier is the current selection. So the expression will try to evaluate with in the selection you have made, and that's where the conflict happens and you get 0 as result.

So it is important that you ignore the Year selection in set analysis. Something like below.

Sum({<Date={">=$(=addyears(Min(Date),-1))<=$(=addyears(Max(Date),-1))"},Year=>}[SalesAmount])

Hope this helps.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

What I see missing in this expression is you haven't ignored the selected fields of master cal.

Meaning if you have selected Year then it will calculate the expression correctly, but by default the Identifier is the current selection. So the expression will try to evaluate with in the selection you have made, and that's where the conflict happens and you get 0 as result.

So it is important that you ignore the Year selection in set analysis. Something like below.

Sum({<Date={">=$(=addyears(Min(Date),-1))<=$(=addyears(Max(Date),-1))"},Year=>}[SalesAmount])

Hope this helps.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
wanyunyang
Creator III
Creator III
Author

Thank you so much!

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Cheers..

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
pwagner
Partner - Creator III
Partner - Creator III

Nice, thank you for your solution.

This helps if you use 'Date Range Picker' and you want to show the data of previous years of the data picked in the extension.

Best regards, Patrick