Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Arithmetic Operation between two diff Aggr and changing the partial sum

Hi All,

I have the following table

Column1Column 2Column3Header 4Header 5
A10100
1.666667
B20200
6.666667
C30300
15
Total
20
600
20( Expected:
23.33333
)

Column 2 : is a SUM(AGGR(SUM [.....])))

Column 3 : is a Average of some value AVG(AGGR(AVG[...]))

Expression in Header 4 : (Column 2 * Column 3)/(Total of Column 3 )


So my problem is in Header 4 : the total basically shows me Column 2 * Column 3 /Column 3

but what i was is the sum of the values in Header 4


Can anyone help me out here?


Thanks,

Jacob


1 Solution

Accepted Solutions
Nicole-Smith

Jacob,

Please see the attached.  It should give you what you're looking for.

sum(aggr(sum(aggr(avg(Sales)*Sum(Target),Country))/sum(aggr(Sum(TOTAL Target),Country)),Country))

View solution in original post

8 Replies
Nicole-Smith

If you're using a straight table, for Expression 4, change the Total Mode to Sum of Rows instead of Expression Total:

Not applicable
Author

Hi Nicole , i am using a pivot table and using partial sum. and the total in the partial sum is messed up

Nicole-Smith

I believe Partial Sums always use expression totals, so you'll either need to mess around with your expression until it gives you the value you want (by summing the rows), or use a straight table instead.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

You will need something like this (assuming Column1 is the single dimension in the table):

Sum(Aggr(

Sum(Aggr(Sum...)) * Avg(Aggr(Avg...)) / Sum(TOTAL Aggr(Avg(Aggr(...)))),

Column1))

The bold sections are your column2 and column3 expressions. All the chart dimensions need to be included in the dimensions of all 5 of  the aggr() expressions.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

Jonathan, i have attached an sample, it doesnt work. Sorry but do you mind helping me out here?

Not applicable
Author

Hey Jacob,

Why should the total of column4 be 23.3333.

per your explanation it should be (20 * 600)  / 600 = 1200/600 = 20  isn't it?

Thanks

AJ

Nicole-Smith

Jacob,

Please see the attached.  It should give you what you're looking for.

sum(aggr(sum(aggr(avg(Sales)*Sum(Target),Country))/sum(aggr(Sum(TOTAL Target),Country)),Country))

Not applicable
Author

Thanks a lot nicole