conditional sum

[#1]

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.

[Anonymous · Accepted solution]

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(

[#3]

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!

[Anonymous]

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.