Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Tags (4)
1 Solution

Accepted Solutions
Not applicable

Re: Avg is Pivot Table

Hey Simky,

Attached with the answer you are looking for.

Thanks

AJ

4 Replies
neetha_p
Honored Contributor

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

Not applicable

Re: Avg is Pivot Table

Hey Simky,

Attached with the answer you are looking for.

Thanks

AJ

Not applicable

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

Thanks again for your help.

Not applicable

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

Community Browser