topic Re: Total SUM in QlikView

Total SUM

Anonymous — Wed, 25 Sep 2013 15:28:47 GMT

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

The problem is the following

23/09/2013

24/09/2013

Total

V3/V1

88.2%

50.0%

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

Anonymous — Wed, 25 Sep 2013 16:07:52 GMT

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

In statistic you cannot calculate the average of percentages.

Re: Total SUM

Anonymous — Wed, 25 Sep 2013 16:13:44 GMT

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?

Re: Total SUM

Oleg_Troyansky — Wed, 25 Sep 2013 19:21:19 GMT

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