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]

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

[#3 · Accepted solution]

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.

[#4]

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?

[#5]

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.

[#6]

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

[#7]

Tnx Jay,

you've helped me very much.

I can go on with my app now