topic Adding row with averages to pivot table in QlikView

Adding row with averages to pivot table

sicilianif — Tue, 15 Sep 2009 00:02:41 GMT

I have a pivot table that shows the total quantity for the last ten days for each plant. Across the top is the plant, along the side is the 10 days. Each value is the total for that day.

At the end I would like to have a row that shows the avg for the last 10 days. Is something like this possible?

Thanks,

Frank

Adding row with averages to pivot table

Oleg_Troyansky — Tue, 15 Sep 2009 00:29:52 GMT

I think you can do it using advanced aggregation. If you modify your Expression along the following lines, and request subtotals for the Day, you should get youat you are looking for:


avg( aggr( sum(Quantity), Plant, Date))

You may also need to condition your formula on the level of detail - only use it when you are showing totals for the Plant. In this case, you'll need to use function Dimensionality():


if(Dimensionality() = 1, // Total by the first dimension
 avg( aggr( sum(Quantity), Plant, Date)),
 sum(Quantity)
)

Adding row with averages to pivot table

stephencredmond — Tue, 15 Sep 2009 00:37:28 GMT

This might be a little simpler - although AGGR is a great function to use if you need correct sum of rows, etc. in a pivot table.

If you set your Sales calculation as:


Sum(Sales)/Count(Distinct Plant)

Then it will just calculate the correct sum on each Plant row (because the Count of Plant = 1) but will give you an average at the total.

Try it and see.

Stephen

Adding row with averages to pivot table

sicilianif — Tue, 15 Sep 2009 00:39:59 GMT

That worked. I had started down that path before, but I did not include both dimensions in the aggr statement, which caused me not to get the results that I was looking for.

I did not have to add the condition.

Thanks,

Frank

Adding row with averages to pivot table

sicilianif — Tue, 15 Sep 2009 00:51:41 GMT

Stephen,

That did not work because the plant is not the row dimension. It is the column.

I tried the same logic with the Date, but could not get it working. I believe it has something to do with the Set Analysis I am doing in the sum.

Thanks,

Frank