Calculate on Expression based on applied Filter

icaro_povoa — Wed, 29 Jan 2020 02:49:05 GMT

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

Re: Calculate on Expression based on applied Filter

Brett_Bleess — Mon, 24 Feb 2020 16:15:36 GMT

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

topic Re: Calculate on Expression based on applied Filter in QlikView

Calculate on Expression based on applied Filter

Re: Calculate on Expression based on applied Filter