Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have this set
=sum({< [Date]={'$(vMaxDate)'}, Year={$(vMaxRevYear)}, Date(Date#([FiscalMonth],'MMM'),'MMM') = {'>= $(vForecastStartMonth)'} >} [AmountForecast] )
The third criteria is giving me and error
Date(Date#([FiscalMonth],'MMM'),'MMM') = {'>= $(vForecastStartMonth)'}
I can't seem to understand what is wrong. Any help?
You are not allowed to use functions on the left hand side of set modifiers
=Sum({<[Date]={'$(vMaxDate)'}, Year={$(vMaxRevYear)}, Date(Date#([FiscalMonth],'MMM'),'MMM') = {'>= $(vForecastStartMonth)'} >} [AmountForecast] )
Why don't you create a field in the script
Month(Date#([FiscalMonth],'MMM'),'MMM') as FiscalMonth,
Num(Month(Date#([FiscalMonth],'MMM'),'MMM')) as FiscalMonthNum,
and then use one of these, preferrable FiscalMonthNum but not sure what vForecastStartMonth contains
=Sum({<[Date]={'$(vMaxDate)'}, Year={$(vMaxRevYear)}, FiscalMonth = {'>= $(vForecastStartMonth)'} >} [AmountForecast])
Try creating 'Date(Date#([FiscalMonth],'MMM'),'MMM')' in script as new field and use that field in set expression.