Need help with latest year flag

[joeallen]

Hi

Trying to create a simple flag in a calendar that filters the date between the last date of the previous month (so 2018-05-31 if max date = today) and the first day of the current month - 12 months (so 2017-06-01 if max date = today).

It should be dependent upon the max date in the selection.

I tried this in script and I only get errors and I dont understand why. When i tried the functions in text box they returned correct values....

if(Date>=MonthStart(Max(Date), - 12) and Date<=MonthEnd(Addmonths(Max(Date),-1)),'1') as LatestYear

[avinashelite]

You cannot use max directly in the script ....try like this

if(Date>=MonthStart(today(), - 12) and Date<=MonthEnd(Addmonths(today(),-1)),'1') as LatestYear

[jyothish8807 · Accepted solution]

Hi Joe,

If you want to make it run based on the month selection, then you should write this logic on the UI and not on backend.

try like this: Create a variable in variable overview

vMaxDate= max(Date)

Then expression:

sum({<Date={'>=$(=date(monthstart(Addmonths($(vMaxDate),-12)))'}<=$(date(monthend(Addmonths($(vMaxDate),-1)))'}>}Sales)

Note: Make sure the date format is correct

Br,

KC

[joeallen]

Thanks Jyothish. That works well for a more dynamic solution.

[avinashelite]

But the above expression doesn't work in script rite ? as per your question you needed something in the script level !!

[jyothish8807]

Yes, this is correct.

He wanted a expression which changes on the month selection he does on the UI also, so i suggested him to do it on the UI.

Br,

KC

[avinashelite]

jyothish8807 your answer is correct I feel he question made the confusion

[jyothish8807]

ya bro