15 Replies Latest reply: Apr 28, 2009 10:56 AM by rey-man

# Subtotals in Pivot don't calculate correct

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?

=(1-

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

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

0)

/sum(budget.budget)

)

)

• ###### Subtotals in Pivot don't calculate correct

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

• ###### Subtotals in Pivot don't calculate correct

`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?

• ###### Subtotals in Pivot don't calculate correct

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

• ###### Subtotals in Pivot don't calculate correct

=
(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.

• ###### Subtotals in Pivot don't calculate correct

ray-man,

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

• ###### Subtotals in Pivot don't calculate correct

I'm sorry, but the data is confidential.

I will try to find another solution.

• ###### Subtotals in Pivot don't calculate correct

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

• ###### Subtotals in Pivot don't calculate correct

`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

• ###### Subtotals in Pivot don't calculate correct

Hi all,

i've made an example file that looks like the original.

It has the same problem.

The rows compute good, but the subtotals are not.

I hope this shows my question right.

Rey-man

• ###### Subtotals in Pivot don't calculate correct

Try this expression in the chart:

avg(aggr( 1-(if(sum(Budget) - sum(Real) >0,sum(Budget) - sum(Real),0)/sum(Budget)) ,Employee,%Date_Key))

Are these the values you want? It's an average of the percentages--add them all up and divide by 5.

• ###### Subtotals in Pivot don't calculate correct

Yes,

the values look right. thank you.

Can you explain why you pick both Employee and %Date_Key in the aggr dimensions and if the order of Employee/%Date_key makes a difference?

In the real application i use two other dimensions: Sector left then Unit next to it. So, i Guess the expression should be:

avg(aggr( 1-(if(sum(Budget) - sum(Real) >0,sum(Budget) - sum(Real),0)/sum(Budget)) ,Sector,Unit)) ?

Or do i have to use the key's also?

• ###### Subtotals in Pivot don't calculate correct

In your case you need to use the same dimensions that are in the chart and nothing more.

The return value of the AGGR() function is a table, or at least that's how I think of it.

aggr( 1-(if(sum(Budget) - sum(Real) >0,sum(Budget) - sum(Real),0)/sum(Budget)) ,Sector,Unit) will return a table that you could almost write in SQL as:

SELECT Sector, Unit, YourExpressionValue FROM QlikViewData GROUP BY Sector, Unit

This table is handed to the Pivot Table object. Where QlikView finds a match for each Sector and Unit it will drop in the value from the aggr table.

• ###### Subtotals in Pivot don't calculate correct

And no, the order of Employee and %Date_Key does not make any difference.

• ###### Subtotals in Pivot don't calculate correct

Tnx Jay,

you've helped me very much.

I can go on with my app now

• ###### Subtotals in Pivot don't calculate correct

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))