Subtotals in Pivot don't calculate correct

[#1]

Hi all,

i've got an expression which calculates if the budget is greather than the reality.

If so, it should calculate is, else take 0.

So far it works. But in the pivot table, the subtotals miscalculate.

I found a post regarding a same sort of problem. (http://community.qlik.com/forums/p/15624/60908.aspx#60908)

Only the dificulty with this one, is that is should calculate if the sum is greather than another sum and not the keyfigure itself.

Can somebody help me?

Tnx in advance.

=(1-

(if(sum(budget.budget) - sum(facts.real) >0,

sum(budget.budget)) - sum(facts.real),

0)

/sum(budget.budget)

)

)

[#2]

Sub totals in pivot tables without a simple sum require an aggreagation - you might want to look at 'aggr' to resolve your problem. If you want to read up about it see 'sum of rows in pivot tables' in book 3 - Charts (P413 in 8.20 ref manual)

Regards,

Gordon

[#3]

gordon.savage wrote:
Sub totals in pivot tables without a simple sum require an aggreagation - you might want to look at 'aggr' to resolve your problem. If you want to read up about it see 'sum of rows in pivot tables' in book 3 - Charts (P413 in 8.20 ref manual)
Regards,
Gordon<div></div>

I tried your solution, but it does'nt work:

= 1-(if(sum(Budget.Budget) - sum(facts.real) >0,avg(aggr(sum(Budget.Budget),EMPLOYEE)) - avg(aggr(sum(facts.real),EMPLOYEE)),0)/avg(aggr(sum(Budget.Budget,EMPLOYEE)))

It calculates the rows good, but the subtotals stay wrong.

Any solution?

[#4]

Two things:

I think there is a missing ')' at the end of the expression

What is the result if you also aggregate the conditional expression ('if(sum(Budget.Budget) - sum(facts.real) >0') ?

Regards,

Gordon

[#5]

=
(1-(if(avg(aggr(sum(Budget.Budget),EMPLOYEE)) - avg(aggr(sum(facts.real),EMPLOYEE)) >0,avg(aggr(sum(Budget.Budget),EMPLOYEE)) - avg(aggr(sum(facts.real),EMPLOYEE)),0)/avg(aggr(sum(Budget.Budget),EMPLOYEE))))

the ) was missing in the reply, but if i create the aggr in the if statement also, it doenst work neither.

[Oleg_Troyansky]

ray-man,

could you please post an example? It would be much easier to help you...

[#7]

I'm sorry, but the data is confidential.

I will try to find another solution.

[Oleg_Troyansky]

you could generate a similar example with random data, or you could scramble the numbers...

[#9]

Seems to me that the entire expression should be surrounded by aggr. The reason that your subtotals are wrong is that "if(sum(Budget.Budget) - sum(facts.real) >0" has meaning on the EMPLOYEE rows but loses its meaning on a subtotal row.

Something like:

avg(aggr(

(1-(if(sum(budget.budget) - sum(facts.real) >0,sum(budget.budget)) - sum(facts.real),0)/sum(budget.budget)))

,EMPLOYEE))

[rwunderlich]

rey-man wrote:
I'm sorry, but the data is confidential.

See: http://qlikviewnotes.blogspot.com/2008/10/example-is-worth-thousand-thread.html for tips on scrambling confidential information.

-Rob