Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
josemaria_cabre
Contributor III
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 PeriodDim1Dim2sum(Value)sum(Aggr(SUM(Value), [Time Period],Dim2))???
2019-01AEurope105845468654686
2019-01BEurope14934054686
2019-01CEurope29168054686
2019-02AEurope105845268352683
2019-02BEurope13395052683
2019-02CEurope28704052683

 

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

Thanks in advance, 

Jpc

Labels (1)
1 Solution

Accepted Solutions
lanlizgu
Creator III
Creator III

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

View solution in original post

9 Replies
tresesco
MVP
MVP

Try incorporating NODISTINCT in aggr() like:

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

lanlizgu
Creator III
Creator III

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

View solution in original post

josemaria_cabre
Contributor III
Contributor III
Author

Worked perfect! Thanks a lot for your help

tresesco
MVP
MVP

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

 

SUM(total <[Time Period]> Value)

josemaria_cabre
Contributor III
Contributor III
Author

Hi Tresesco

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

Time PeriodDim1Dim2Sum(Value)sum(Aggr(NODISTINCT SUM(Value), [Time Period],[Dim2]))SUM(total <[Time Period]> Value)
2019-01AEurope105843062416107369
2019-01BEurope149344429566107369
2019-01CEurope291688421644107369
2019-02AEurope105842950248107369
2019-02BEurope133953793176107369
2019-02CEurope287048113182107369
tresesco
MVP
MVP

It works with me:

Capture.PNG

josemaria_cabre
Contributor III
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?

 

tresesco
MVP
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.

josemaria_cabre
Contributor III
Contributor III
Author

Perfect! Thanks for your help Tresesco.