topic Re: Avg is Pivot Table in QlikView

Avg is Pivot Table

Thu, 20 Feb 2014 21:41:21 GMT

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

Anonymous — Thu, 20 Feb 2014 21:47:47 GMT

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

Thu, 20 Feb 2014 22:54:31 GMT

Hey Simky,

Attached with the answer you are looking for.

Thanks

Re: Avg is Pivot Table

Fri, 21 Feb 2014 00:14:22 GMT

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.

Re: Avg is Pivot Table

Fri, 21 Feb 2014 00:32:12 GMT

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