topic Help with aggr & sum total in App Development

Help with aggr & sum total

josemaria_cabre — Sat, 16 Nov 2024 19:35:34 GMT

Hi,

I have the following table, and I want to calculate Value, grouped by Time Period and Dim2. I tried with: sum(Aggr(SUM(Value), [Time Period],Dim2)) but it didn't work.

Time Period	Dim1	Dim2	sum(Value)	sum(Aggr(SUM(Value), [Time Period],Dim2))	???
2019-01	A	Europe	10584	54686	54686
2019-01	B	Europe	14934	0	54686
2019-01	C	Europe	29168	0	54686
2019-02	A	Europe	10584	52683	52683
2019-02	B	Europe	13395	0	52683
2019-02	C	Europe	28704	0	52683

How do I calculate the '???' column? Can anyone pls help?

Thanks in advance,

Jpc

Re: Help with aggr & sum total

tresB — Mon, 25 Nov 2019 12:16:54 GMT

Try incorporating NODISTINCT in aggr() like:

sum(Aggr(NODISTINCT SUM(Value), [Time Period],Dim2))

Re: Help with aggr & sum total

lanlizgu — Mon, 25 Nov 2019 12:21:01 GMT

sum(Aggr(SUM(total <[Time Period]> Value), [Time Period],Dim1,Dim2))

Re: Help with aggr & sum total

josemaria_cabre — Mon, 25 Nov 2019 12:25:08 GMT

Worked perfect! Thanks a lot for your help

Re: Help with aggr & sum total

tresB — Mon, 25 Nov 2019 12:26:50 GMT

In fact, you would not probably require aggr() here. Just try:

SUM(total <[Time Period]> Value)

Re: Help with aggr & sum total

josemaria_cabre — Mon, 25 Nov 2019 13:23:09 GMT

Hi Tresesco

I tried with both expressions, but they didn't work for me

Time Period	Dim1	Dim2	Sum(Value)	sum(Aggr(NODISTINCT SUM(Value), [Time Period],[Dim2]))	SUM(total <[Time Period]> Value)
2019-01	A	Europe	10584	3062416	107369
2019-01	B	Europe	14934	4429566	107369
2019-01	C	Europe	29168	8421644	107369
2019-02	A	Europe	10584	2950248	107369
2019-02	B	Europe	13395	3793176	107369
2019-02	C	Europe	28704	8113182	107369

Re: Help with aggr & sum total

tresB — Mon, 25 Nov 2019 13:35:26 GMT

It works with me:

Re: Help with aggr & sum total

josemaria_cabre — Mon, 25 Nov 2019 13:47:13 GMT

In my example, I have filtered Time Period '201901' and '201902', and Dim2='Europe' but I have other Time Periods (201903, 201904 ...) and other Dim1 (America, Africa ...) in my dataset.

Could that be the reason maybe?

Re: Help with aggr & sum total

tresB — Mon, 25 Nov 2019 13:56:17 GMT

Filtering period should not be the issue, however, different values in dim2 should be one. If you need output wrt the values in dim2, you have to include dim2 too in the expression.

Re: Help with aggr & sum total

josemaria_cabre — Mon, 25 Nov 2019 13:57:55 GMT

Perfect! Thanks for your help Tresesco.