topic Re: conditional sum in QlikView

conditional sum

Mon, 06 Jan 2014 02:13:42 GMT

Hi,

Can somebody help me with calcurating subtotal in pivot table. Please find the attatchment.

I asked a question before ( subtotal for calcuated row )

and learned that position of "sum" effect the result.

A)sum(Cost2/Number2* Number1) it worked to get subtotal.

B)sum(cost2)/sum(Number2)*sum(Number1) it doesn't.

Now, What I'd like to know is how I can use conditional expression in above A expression.

I used sum(total <A,B>) expression before and I don't know what should I do without "sum".

the expression I used before was...

(Sum(TOTAL<year, age, Dname> {<condition1={'A'}>} Cost2 )

/ Sum(TOTAL<year, age> {<conditon1={'A'}>} Number2 )

)* Sum(Number1)

How can I adjust the expression A to this? or How can I add conditions in expression A?

(There isn't [conditon1] in the chart attatched but there's another table which has [condition1].

[condition1 = "A"] just means main office and "B" means subordinate.)

Thank you in advance.

Re: conditional sum

Anonymous — Mon, 06 Jan 2014 02:55:44 GMT

Not sure if this will work but try

SUM (AGGR (

Sum(TOTAL<year, age, Dname> {<condition1={'A'}>} Cost2 )

/ Sum(TOTAL<year, age> {<conditon1={'A'}>} Number2 )

)* Sum(Number1), Office, Age))

Office and Age being the Dimensions in the Pivot. If that doesn't work maybe just SUM( without the AGGR(

Re: conditional sum

Mon, 06 Jan 2014 05:02:56 GMT

Thank you Michael,

Though I need to add <Year and Dname> as dimension in the expression,

it worked well.

SUM (AGGR (

Sum(TOTAL<year, age, Dname> {<condition1={'A'}>} Cost2 )

/ Sum(TOTAL<year, age> {<conditon1={'A'}>} Number2 )

)* Sum(Number1), Office, Age, Year, Dname))

Thanks a lot!

Re: conditional sum

Anonymous — Mon, 06 Jan 2014 05:39:40 GMT

Glad it worked. I've never tried it myself so I wasn't confident but the solution was located in the book "QlikView for Developers Cookbook" chapter 4 in case you were wondering.