Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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(
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(
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!
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.