topic Re: Show Variance from Average in an additional column in QlikView

Show Variance from Average in an additional column

Fri, 24 Jul 2015 03:28:28 GMT

Hi all,

Tanks for you time for viewing this post.

I have a pivot table as shown below.

There are 6 dimensions in total. The CPU is the expression column. I am displaying the AVERAGE by enable partial sum on Vendor and some manipulation on dimensionality(). The expression is as below.

so it will pick expression for each dimensionality.

Now what I want to do is to add an additional column to show the variance of the CPU from the AVERAGE value below it. Is it possible to do it based on what I have now? Or is there any other alternate to do it?

Thanks.

Re: Show Variance from Average in an additional column

swuehl — Fri, 24 Jul 2015 07:50:38 GMT

Are you sure you need this complex average calculation?

Won't you get the same results using

pick(dimensionality(),NULL(),NULL(),NULL(),NULL(),

Avg ( CPU)

,CPU)

Or maybe

pick(dimensionality(),NULL(),NULL(),NULL(),NULL(),

Avg ( aggr( CPU, Main_Cat, Sub_Cat, Task_Cat, Task_Name_C, Revision, Language_C, Vendor))

,CPU)

If yes, just replace Avg() by Stdev() to calculate the standard deviation, square this result to get variance.

Re: Show Variance from Average in an additional column

Fri, 24 Jul 2015 08:24:26 GMT

Hi,

Thanks for you reply!

The second expression u gave to calculate the average returns the correct results.

The first one does not work because there is actually a set of the same CPU of different tasks for each vendor, the sum of CPU would be divided by the number of tasks in the set, but not the number of vendors.

As for the variance, I didn't expressed it clearly. What I want is an additional column after the column CPU, showing the difference of each of the CPU to the AVERAGE below it. For example, for the first three rows, it will be like

Vendor 80 Difference

Alpha 18.00 0.231 (18-17.769)

Pactera 17.539 -0.23 (17.539-17.769)

AVERAGE 17.769

Re: Show Variance from Average in an additional column

swuehl — Fri, 24 Jul 2015 08:33:53 GMT

You would need the TOTAL qualifier with a field list, something like

pick(dimensionality(),NULL(),NULL(),NULL(),NULL(),

CPU - Avg (TOTAL<Main_Cat, Sub_Cat, Task_Cat, Task_Name_C, Revision, Language_C> aggr( CPU, Main_Cat, Sub_Cat, Task_Cat, Task_Name_C, Revision, Language_C, Vendor))

)

Re: Show Variance from Average in an additional column

Fri, 24 Jul 2015 09:03:39 GMT

Thanks! The TOTAL qualifier does the trick!