Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Partner Ambassador
Partner Ambassador

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

View solution in original post

5 Replies
michael_maeuser
Partner Ambassador
Partner Ambassador

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

Not applicable
Author

Hi,

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

Thanks and Regards,

S.Amuthabharathi

Not applicable
Author

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
Author

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
Author

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