Help with aggr & sum total

[josemaria_cabre]

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

[tresesco]

Try incorporating NODISTINCT in aggr() like:

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

[lanlizgu · Accepted solution]

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

[josemaria_cabre]

Worked perfect! Thanks a lot for your help

[tresesco]

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

SUM(total <[Time Period]> Value)

[josemaria_cabre]

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

[tresesco]

It works with me:

[josemaria_cabre]

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?

[tresesco]

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.

[josemaria_cabre]

Perfect! Thanks for your help Tresesco.