Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set analysis with 2 date Variables

Hi Everyone,

I am after some expert help if possible. I am trying to write a sum in set analysis that allows me to filter my data between 2 date variables. I have been able to get the set analysis to work using hard coded dates but i need to use a variable to remove the manual intervention once my app is live.

The calculation that fails to work is :

Sum({1<ReportDate= {">= $(=vCYmin11) <= $(=vStartCY) "}, Versions = {"$(vVersion1)"} , [Line Items] = {'PIF Count (#)'} >} Data)

It works like this: 

Sum({1<ReportDate = {">=01/02/2019<=01/01/2020"}, Versions = {"$(vVersion1)"} , [Line Items] = {'PIF Count (#)'} >} Data)

1st Variable is (vCYmin11) :  Date(AddMonths(YearStart(Today()),-11),'DD/MM/YYYY')

2nd Variable is (vStartCY) : Date(YearStart(Today()),'DD/MM/YYYY')

I can not seem to get it to work with the variables? Thank you in advance of any advice!

4 Replies
Highlighted
Specialist II
Specialist II

Re: Set analysis with 2 date Variables

Maye be :

Sum({1<ReportDate= {">=$(=Date('$(vCYmin11)')) <=$(=Date('$(vStartCY)'))"}>}, Versions = {"$(vVersion1)"} , [Line Items] = {'PIF Count (#)'} >} Data)
Regards,
Taoufiq ZARRA
Highlighted
Contributor
Contributor

Re: Set analysis with 2 date Variables

Thank you for your reply Taoufig_Zarra. I have tried your method and it did not seem to work. Maybe a better approach would be....does anyone else have a working example that I could analyse to try and pick up any variations?

just to add! there was a >} added after the date variable part, removing this and the calculation works but for the full year, like it is not reading the month section of the calc?

Highlighted
Specialist II
Specialist II

Re: Set analysis with 2 date Variables

Please try this,

Sum({1<ReportDate= {">=$(=vCYmin11)><=$(=vStartCY)"},Versions = {"$(vVersion1)"} , [Line Items] = {'PIF Count (#)'} >} Data)

If it's still not work then please remove other two filters, that is vVersion and Line items and check it.

Thanks,

Vishal Waghole

Highlighted
Specialist II
Specialist II

Re: Set analysis with 2 date Variables

@Fletch 

can you share a sample data ?

Regards,
Taoufiq ZARRA