Aggregation by previous year

[fabrice_lattern]

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 !

[deepanshuSh]

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.

[Timario]

Hi, try next expression

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

[Timario]

if you need only certain years you can use 

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

[fabrice_lattern]

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).

[fabrice_lattern · Accepted solution]

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).