Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

wanyunyang
Contributor

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

Re: master calendar doesn't work

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

3 Replies

Re: master calendar doesn't work

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

wanyunyang
Contributor

Re: master calendar doesn't work

Thank you so much!

Re: master calendar doesn't work

Cheers..

Regards,

Kaushik Solanki

Community Browser