Greater than set analysis with filter selection

[iczkla]

Hello,

I am trying to create a calculation with set analysis which will count ID's for the last 12 months based on the Dated selected in filter.

My date is in format 'YYYY-MM' and expression I've created looks like this:

count({$<[Report Period]={">=$(=date(addmonths(date#(only([Report Period]),'YYYY-MM'),-11),'YYYY-MM'))"}>} [ID])

For some reason it returns 0 result. When I use it without > sign it works:

count({$<[Report Period]={"=$(=date(addmonths(date#(only([Report Period]),'YYYY-MM'),-11),'YYYY-MM'))"}>} [ID])

I have also tried it without $ but same results.

Any suggestions will be much appreciated. Thank you!

[chirag-pradhan]

I think your Report Period field is a Text data type. And you are trying to compare a text to a date. I'd suggest converting the Report Period field to a date in the script first, then applying your original expression, and it should work.

[iczkla]

Thanks for your replay Chirag. 

Would you be able to avise how I can convert this to date? This is what I currently doing in the script:

Date(MonthStart([Date]),'YYYY-MM') as [Report Period]

[chirag-pradhan · Accepted solution]

No problem. Try this in the script. Leave your set analysis expression as-is - 

Date(Date#([Report Period],'YYYY-MM'),'YYYY-MM') as [Report Period]

[iczkla]

Worked like a charm, thank you!