Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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]
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.
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]
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]
Worked like a charm, thank you!