3 Replies Latest reply: Sep 25, 2013 4:06 PM by Oleg Troyansky

# Total SUM

Hello I have a problem with qlik and when I put the partial sums of the system.

The problem is the following

 D3 23/09/2013 24/09/2013 Total D1 D2 V1 V2 V3 V3/V1 V1 V2 V3 V3/V1 V1 V2 V3 V3/V1 B A 17 16 15 88.2% 8 7 4 50.0% 25 23 19 76.0%

The system total show 76%, it's normal, but I need (88.2+50)/2= 69.1

My solution is add a column whit this (before([V3/V1]) +[V3/V1]) /2, but is not good for me.

You have suggestions?

• ###### Re: Total SUM

Qlikview give the correct answer: 19/25=76%.(sum(v3)/sum(v1))

In statistic you cannot calculate the average of percentages.

• ###### Re: Total SUM

I know it's a correct answer, but there is a way to get the result by adding a field without using before?

• ###### Re: Total SUM

If I understand you correctly, QlikView calculates the "Expression Total" at your subtotal levels and you'd like to calculate the average of the subtotals instead...

You can use Advanced Aggregation (funciton AGGR) to pre-calculate the subtotals at the relevant level of detail, and then to apply average to the pre-calculated results: something like this:

AVG( AGGR ( sum(V3)/SUM(V1), D1, D2) )

Usually the same expression should work equally well for all levels. If it doesn't, then you can use Dimensionality() to define what calculation to use at what level:

IF (Dimensionality() = 0, <Formula1>, <Formula2>)

cheers,

Oleg Troyansky