Total SUM

[Anonymous]

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?

[Anonymous]

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

In statistic you cannot calculate the average of percentages.

[Anonymous]

thanks for the reply.

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

[Oleg_Troyansky]

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

www.masterssummit.com