Working with values that have already been cumulated

[danaleota1]

Hello, 

I have a dataset that gives FYTD values so they have already been accumulated.  In order to show metric by fiscal year I want to grab the last month's value for that year.

I tried the following 

aggr(avg({< Rev_Date = {'$(=Date(max(Rev_Date), ''))'} >} Revenue), FiscalYear)

but it's not returning the revenue of the last month by Fiscal Year 

any suggestions?

Thanks

[rubenmarin1 · Accepted solution]

Hi, this might be esier if you flag the dates of the last month of each fiscal year in the script, so you only need something like:avg({<isFYLastMonth={1}>} Revenue)

About the aggr: set analysis is expanded before calculating the expression, so it will only return the max overall date, not by FiscalYear. You can use If() to compare with somthing like Max(TOTAL <FiscalYear> Rev_Date) but this might have an impact on performance.

Also Aggr retunrs a different value for each dimension values set as parameters, so ususally it needs an aggregation to tell what to do with all those values

[danaleota1]

Thanks @rubenmarin1 for your recommendation.   I did end up making a flag in my Master Calendar,

"if(TempDate = monthstart(yearend(TempDate, 0, 10)), 1,0) as LastDateFlag,"

and then calling that flag in my set analysis "Avg({< LastDateFlag = {'1'} >} Revenue)"

and is working for my report.  

Thanks