4 Replies Latest reply: Feb 20, 2014 7:32 PM by Ajay Krishnan Prabhakaran

# 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

• ###### Re: Avg is Pivot Table

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

• ###### Re: Avg is Pivot Table

Hey Simky,

Attached with the answer you are looking for.

Thanks

AJ

• ###### Re: Avg is Pivot Table

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

• ###### Re: Avg is Pivot Table

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