Result of previous year in a aggr(only()) function

BiancaWou — Mon, 08 May 2023 12:42:34 GMT

I have this function including aggr and only

sum(aggr(only({<InstalledBaseFlag={1}, InstalledBaseType -={DeinstallNew}>}_InstalledBaseCumul), MonthYear, InstalledBaseType, ProdHier1, SoldTo, Region, Product))

This formula gives me the result of the selected Month and Year, example April 2023

Now, I would like to compare this with the result of the previous year (selection will still be April 2023, but I would like to see the numbers of April 2022 with the function AddMonths -12

If I use this formula

sum(aggr(only({<<MonthYearNum=
"$(=Num(Max(AddMonths(MonthYearNum,-12))))"},Year=,Month=,MonthYear=,Day=,YTD,InstalledBaseFlag={1}, InstalledBaseType -={DeinstallNew}>}_InstalledBaseCumul),
MonthYear, InstalledBaseType, ProdHier1, SoldTo, Region, Product))

I get no result

This formula without the aggr() is working(but incorrect), but when I add the aggr and only, it is not working

sum({<MonthYearNum={"$(=Num(Max(AddMonths(MonthYearNum,-12))))"},Year=,Month=,MonthYear=,Day=,YTD,InstalledBaseFlag={1}, InstalledBaseType -={DeinstallNew}>}_InstalledBaseCumul)

How can I get from this formula

sum(aggr(only({<InstalledBaseFlag={1}, InstalledBaseType -={DeinstallNew}>}_InstalledBaseCumul), MonthYear, InstalledBaseType, ProdHier1, SoldTo, Region, Product))

to the result of the previous year (when selected a year and a month)

Thanks,

Bianca

Re: Result of previous year in a aggr(only()) function

Chanty4u — Mon, 08 May 2023 14:20:16 GMT

Try this

sum(aggr(

only({<InstalledBaseFlag={1}, InstalledBaseType-={DeinstallNew}, MonthYearNum = {"$(=Num(Max(AddMonths(MonthYearNum, -12))))"}>}_InstalledBaseCumul),

InstalledBaseType, ProdHier1, SoldTo, Region, Product

))

Re: Result of previous year in a aggr(only()) function

BiancaWou — Mon, 08 May 2023 15:35:32 GMT

Dear Chanty4u

Thank you for the formula.

This is working, but only if you also select the previous year. (2023 + 2022)

If you only select 1 year and 1 or more months, you get no result (0)

Bianca

topic Re: Result of previous year in a aggr(only()) function in App Development

Result of previous year in a aggr(only()) function

Re: Result of previous year in a aggr(only()) function

Re: Result of previous year in a aggr(only()) function