topic Re: Working with values that have already been cumulated in App Development

Working with values that have already been cumulated

danaleota1 — Thu, 14 Dec 2023 19:06:01 GMT

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

Re: Working with values that have already been cumulated

rubenmarin — Thu, 14 Dec 2023 20:05:55 GMT

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

Re: Working with values that have already been cumulated

danaleota1 — Fri, 15 Dec 2023 14:52:21 GMT

Thanks @rubenmarin 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