topic Re: Sum of Rows in Text Box in QlikView

Sum of Rows in Text Box

Wed, 16 Dec 2015 18:10:40 GMT

Hi ,

Is there anyway to replicate the Sum of Rows in a Straight chart into a Text Box.

The problem I am facing is,the Total of an Expression is different when you individually calculate the expression for each quarter.

Any help would be greatly appreciated

Re: Sum of Rows in Text Box

Gysbert_Wassenaar — Wed, 16 Dec 2015 18:13:42 GMT

You can use the aggr function: sum(aggr(sum(Something),Dimension1, Dimension2, ...etc)

Re: Sum of Rows in Text Box

maxgro — Wed, 16 Dec 2015 18:17:13 GMT

try

=sum(aggr(sum(unitPerItem

[Unit Price] *

[Rejected Items]), Date))

Re: Sum of Rows in Text Box

sunny_talwar — Wed, 16 Dec 2015 18:18:57 GMT

Gysbert - Isn't that the same thing as Sum(Something) unless you have a distinct -> Sum(Aggr(Sum(DISTINCT Something), Dimension1, Dimension2, ...etc)

Re: Sum of Rows in Text Box

Wed, 16 Dec 2015 18:21:25 GMT

I tried that,its not working in my case,wit the above example following expression is used in the text box.

=(sum(aggr(sum({<Test-={'Actual'},BrandId=p(DealId)>}CostPerBundle),Quarter,Id))/

sum(aggr(sum({<Test-={'Actual'},BrandId=p(DealId)>}TotaUnits),Quarter,Id)))

*sum(aggr(sum({<Test={'Actual'},BrandId=p(DealId)>}Rejected Items),Quarter,Id))*

sum(aggr(avg({<Test={'Actual'},BrandId=p(DealId)>}unitPerItem),Quarter,Id))

Re: Sum of Rows in Text Box

sunny_talwar — Wed, 16 Dec 2015 18:26:19 GMT

Give this a try:

=Sum(Aggr(

((Sum({<Test-={'Actual'}, BrandId=p(DealId)>} CostPerBundle)/Sum({<Test-={'Actual'}, BrandId=p(DealId)>} TotaUnits)) * Sum({<Test={'Actual'}, BrandId=p(DealId)>} Rejected Items) * Avg({<Test={'Actual'}, BrandId=p(DealId)>} unitPerItem)),

Quarter,Id))

Re: Sum of Rows in Text Box

Gysbert_Wassenaar — Wed, 16 Dec 2015 19:19:01 GMT

It depends on the Something.

Re: Sum of Rows in Text Box

sunny_talwar — Wed, 16 Dec 2015 19:21:07 GMT

So you mean if it is field or a bunch of calculations (like in his case a division and multiplication)?

Re: Sum of Rows in Text Box

Wed, 16 Dec 2015 20:55:53 GMT

Thanks Maxgro.....it worked well when I am just using the tree fields,but when I use the Set Analysis condition for all the three fields its not working.

Any help

Re: Sum of Rows in Text Box

sunny_talwar — Wed, 16 Dec 2015 20:57:36 GMT

Did you try this John?

=Sum(Aggr(

Quarter,Id))

Re: Sum of Rows in Text Box

Wed, 16 Dec 2015 21:15:35 GMT

Sorry Sunny....tried that but it returns 0 for some reason.

sum(aggr(sum({<Scenario={'Actual'}>}Clms_Vol)*sum({<Scenario-={'Actual'}>}Unit*sum({<Scenario-={'Actual'}>}UnitPrice)),Quarter))

The above is what I require,unfortunately its not giving any output

Re: Sum of Rows in Text Box

sunny_talwar — Wed, 16 Dec 2015 21:20:32 GMT

I don't know if this is a copy paste error, but you are missing a parenthesis here:

Sum(Aggr(

(Sum({<Scenario={'Actual'}>}Clms_Vol) *

Sum({<Scenario-={'Actual'}>}Unit) *

Sum({<Scenario-={'Actual'}>}UnitPrice)),

Quarter))

Re: Sum of Rows in Text Box

Wed, 16 Dec 2015 21:34:43 GMT

its not taking the third expression for some reason

Re: Sum of Rows in Text Box

Wed, 16 Dec 2015 22:01:46 GMT

I think,it was the problem with the fields and the setanalysis condition....but it works fine now...Thanks Sunny