Announcements
Applications are open for the 2024 Qlik Luminary Program. Apply by December 15 here.
cancel
Showing results for
Did you mean:
Contributor III

## Help with aggr & sum total

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?

Jpc

Labels (1)
• ### sum aggr total dimensions

1 Solution

Accepted Solutions
Creator III

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

9 Replies
MVP

Try incorporating NODISTINCT in aggr() like:

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

Creator III

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

Contributor III
Author

Worked perfect! Thanks a lot for your help

MVP

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

SUM(total <[Time Period]> Value)

Contributor III
Author

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
MVP

It works with me:

Contributor III
Author

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?

MVP

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.

Contributor III
Author

Perfect! Thanks for your help Tresesco.

Tags
Community Browser