Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Create a Calculated Dimension in Pivot Table?

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?

1 Solution

Accepted Solutions
michael_maeuser
Contributor III

Re: Create a Calculated Dimension in Pivot Table?

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

5 Replies
michael_maeuser
Contributor III

Re: Create a Calculated Dimension in Pivot Table?

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

Not applicable

Re: Create a Calculated Dimension in Pivot Table?

Hi,

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

Thanks and Regards,

S.Amuthabharathi

Not applicable

Re: Create a Calculated Dimension in Pivot Table?

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

Not applicable

Re: Create a Calculated Dimension in Pivot Table?

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)

Not applicable

Re: Create a Calculated Dimension in Pivot Table?

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

Community Browser