Set analysis with 2 date Variables

[Fletch]

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!

[Taoufiq_Zarra]

Maye be :

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

[Fletch]

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?

[VishalWaghole]

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

[Taoufiq_Zarra]

@Fletch 

can you share a sample data ?

[Fletch · Accepted solution]

hi all thanks for the responses, i have thought of another way around this to make it easier, i am going to use the calendar in the script to flag the dates so that in the set analysis i can just indicate where my new field equals 1, rather than over complicate it.

thanks for all your help.