topic Re: Create a Calculated Dimension in Pivot Table? in QlikView

Create a Calculated Dimension in Pivot Table?

Mon, 02 Sep 2013 06:43:50 GMT

I have three dimensions. 'Product','ArticleNo','Week' and an expression 'Units Sold'. The table is pivoted on 'Week' dimension with 'Units Sold' is its measure of expression. Now I need to add average as a Calculated Dimension, which should calculate overall average of 'Units Sold'. Anyone, how can I do this?

Re: Create a Calculated Dimension in Pivot Table?

michael_maeuser — Mon, 02 Sep 2013 06:50:59 GMT

aggr(avg(unitssold), product, article, week)

Re: Create a Calculated Dimension in Pivot Table?

Mon, 02 Sep 2013 07:23:12 GMT

Hi,

Sum(unitssold)/Sum({<product=, article=, week=>}unitssold). Try this I hope it helps you

Thanks and Regards,

S.Amuthabharathi

Re: Create a Calculated Dimension in Pivot Table?

Mon, 02 Sep 2013 07:48:27 GMT

Hi,

What dimensions do you want to calculate the average over? You can use the TOTAL modifier to ignore dimensions in the chart for example:

Average(TOTAL<Product, ArticleNo>Units Sold) will give you the average Units Sold calculated over the Product and Article dimensions only, ignoring the Week Dimension.

Also just noticed in your original post you mentioned calculated dimension. Are you sure you want to calculate a dimension in this case or are you wanting to create an expression to calculate the average over a specific set of dimensions?

Andy

Re: Create a Calculated Dimension in Pivot Table?

Thu, 05 Sep 2013 08:27:44 GMT

what if i want average for, lets say, week 1 and 2. How do I do that, using below expression?

aggr(avg(unitssold), product, article, week)

Re: Create a Calculated Dimension in Pivot Table?

Fri, 06 Sep 2013 10:33:22 GMT

I'm not too sure how you would do that for specific weeks without creating a multitude of expressions. Although in a pivot table you can refer to the adjacent cell using the "Last" function. Look it up in the chart inter record functions in help.

The only thing I can thing of with the expression that you have would be to use something like

aggr(avg(unitssold), product, article, month)

This would then calculate the average for the month that those weeks belonged too.

Would that work for you?

Andy