Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I am trying to create a TextBox with a calculation that Represents the sum of events that happen on previous year.
For not Filtering with a text and need to adjust this every year, I was thinking if there is a way to make this dynamic according to the filter.
Example: if I select Year=2019, my expression on the text box would be related to 2018. If select 2020, would be 2019.
The expression with Fixed values works:
=sum(TOTAL{$<[Year]={'2018'},Month={'01 (Jan)','02 (Feb)','03 (Mar)','04 (Apr)','05 (May)','06 (Jun)','07 (Jul)','08 (Aug)','09 (Sep)','10 (Oct)','11 (Nov)','12 (Dec)'}>}[Event])
Currently I am trying this expression for making it dynamic, but without success.
=sum(TOTAL{$<[Year]={"=[Year]-1"},Month={'01 (Jan)','02 (Feb)','03 (Mar)','04 (Apr)','05 (May)','06 (Jun)','07 (Jul)','08 (Aug)','09 (Sep)','10 (Oct)','11 (Nov)','12 (Dec)'}>}[Event)
Can you please help?
Thanks
Check the following Design Blog post, your syntax is not quite matching up to what is listed there, so hopefully tweaking things will get it working:
https://community.qlik.com/t5/Qlik-Design-Blog/Quotes-in-Set-Analysis/ba-p/1471824
The only other thing of which I can think is to be sure your Year field is in fact numeric, that could be the other issue here in order to be able to subtract one, you may need an alias field where you convert it using Num()... Hopefully one of these two works.
Regards,
Brett