Show Variance from Average in an additional column

[#1]

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.

[swuehl]

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.

[#3]

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

[swuehl · Accepted solution]

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

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

0,

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))

)

[#5]

Thanks! The TOTAL qualifier does the trick!