topic Rolling Sum in Stacked Bar Chart in App Development

Rolling Sum in Stacked Bar Chart

BoXiangWang — Tue, 25 Feb 2025 04:52:25 GMT

Hi all QS experts,

I have an issue with the rolling 12 month bar chart.

After applying the expression below, I found there are two issues:

(1) It doesn't roll up, as you can see there are 0s in certain months

(2) The accumulation figures will change depending on the Dimension user selected

(1) 0s showing in the bar chart

(2) After changing the dimension to B, the 2024-Dec number becomes 31, instead of 27, which 27 is accurate

Could you help check where could be the problem? I really don't get it.

Expression
=

IF(date_month<'$(=AddMonths(min(date_month),($(vRollingMonths)-1)))', null(),

{<[date_month]={"<=$(=MonthEnd(vRollingDate))"}>}

aggr(

Rangesum(Above(Count(Distinct {<leaver={1}, secondee=$(vSecondee)>} [dim_ppl_employee_sk]), 0, $(vRollingMonths)))

,$(vDimensionTurnover), date_month)

)

Re: Rolling Sum in Stacked Bar Chart

Oleg_Troyansky — Tue, 25 Feb 2025 16:40:26 GMT

Hi,

I believe the problem is related to using the Above() function in a multi-dimensional chart (or AGGR, for that matter). By default, the Above() function works within the "current segment" of the two dimensions. In order to make it work across multiple dimensions, add the TOTAL qualifier to the Above().

Another possible issue is the inner Set within the AGGR, which is not repeated outside of the AGGR. Based on the logic of your formula, you should add the Set defined in the Count function, to the outer set that you specify before the AGGR - then it will apply correctly to the whole calculation.

Other than that, there could be errors with variables, field names, etc... - it's hard to predict all the possible issues without seeing the App.

Cheers,