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

Avg is Pivot Table


Hi,

I have 3 dimensions in a pivot table, with Qty as measure. I am sowing sum(Qty) for last 3 months by country and category. Now i would like show avg(Qty) for last 3 months by Country anad Category. I can create two separte tables, and show the last 3 months Qty and avg. But am not able to do in a single table. So the question is, can we do avg by different dimensions instead of sum in a pivot table.

I have attached my QV and required view for reference.

Thanks

1 Solution

Accepted Solutions
Not applicable
Author

Hey Simky,

Attached with the answer you are looking for.

Thanks

AJ

View solution in original post

4 Replies
Anonymous
Not applicable
Author

Hi Simky,

Use rolling n-period,Check below link to calculate 3 months avg for qty Calculating rolling n-period totals, averages or other aggregations

Not applicable
Author

Hey Simky,

Attached with the answer you are looking for.

Thanks

AJ

Not applicable
Author

Hi Ajay,

Thanks for the direction. I made slight changes, since the dashboard would be in server with automatic update, so I cant put hardcore monthname.

Max Month Expression :

  =sum( {$<[Month_ID]= {$(vMaxMonthID)}>} Qty )

Similarly Prev Month Expresssion:

=sum({$<[Month_ID]= {$(vMaxMonthID_1)}>} Qty)

where vMaxMonthID=max(Month_ID)  and vMaxMonthID_1=max(Month_ID) -1

Thanks again for your help.

Not applicable
Author

Absolutely.

I just did it as an example. Never hard code numbers/texts that will change over time..

The way you did it by using variables is the best approach.

Thanks

AJ