topic Re: Aggregation by previous year in App Development

Aggregation by previous year

fabrice_lattern — Fri, 02 Sep 2022 14:58:25 GMT

Hello !

My data is composed of an amount of sales per year, month and product. I am trying to create a table where we could see, by year and product, the amount of sales in december of the previous year, like in the following example :

I used the following formula :

only(aggr(above(sum( {<Month={12}>}Sales),1,rowno()),Product,Year))

Which works well when we have december data for the year. The issue is that for 2022, I do not yet have december data, and the formula seems not to retrieve 2021 december data.

Do you have any idea on the way to make it work for the ongoing year ?

Thanks in advance for any idea !

Re: Aggregation by previous year

deepanshuSh — Fri, 02 Sep 2022 17:02:14 GMT

Maybe this can work, if you just want to showcase the current year consolidated data, then create a variable having max(year) and put it in place of the month = 12, then it could work. For month, create a month-year column in the master calendar in the datascript and then use it for the month calculation.

Re: Aggregation by previous year

Timario — Sat, 03 Sep 2022 06:21:04 GMT

Hi, try next expression

sum({<Year, Year = {"<$(=Year(today()))"}, Month={12}>} Sales )

Re: Aggregation by previous year

Timario — Sat, 03 Sep 2022 06:21:22 GMT

if you need only certain years you can use

sum({<Year, Year = {'2019', '2021'}, Month={12} >} Sales )

Re: Aggregation by previous year

fabrice_lattern — Mon, 05 Sep 2022 07:15:50 GMT

Hello,

Thanks for the help, but this is not responding to the requirement, because you are not displaying the N-1 december result in front of year N (here you display december N in front of year N).

Re: Aggregation by previous year

fabrice_lattern — Mon, 05 Sep 2022 07:19:28 GMT

I found a solution (not totally statisfing as it has an "if" condition, but it works) :

if(Only({1}Year) = $(vYearN), SUM(TOTAL<Product> aggr (above(sum( {<Month={12}>}Sales),1,rowno()),Product,Year)), Only(aggr (above(sum( {<Month={12}>}Sales),1,rowno()),Product,Year)))

Where vYearN is a variable defined as =Max({1}Year).