Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Calculate on Expression based on applied Filter

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

1 Reply
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.