topic Re: Average as last column in Pivot in QlikView

Average as last column in Pivot

mike_spada — Sun, 21 Mar 2021 16:51:11 GMT

Hi everyone,

I need to create a pivot table and I'm stuck for a thing that I thougth it was easy... how can I insert a column - like the "Totals" in pivot - with Average instead of total sum?

I need to have something like this:

working collapsed too:

Can someone help me please?

I attach a qvw as sample.

Thank you in advance!

Mike

Re: Average as last column in Pivot

Frank_Hartmann — Sun, 21 Mar 2021 17:56:45 GMT

Maybe like attached.

add an Inline table to the script:

Load * Inline [

dim

];

then use following 3 dimensions: Area, Year, pick(dim,Month,'Avg')

expression: If(pick(dim, Amount, 'Avg')<>'Avg', sum({ <Customer>}Amount),sum(Total <Customer,Area> Amount) /count(DISTINCT( Month)))

Re: Average as last column in Pivot

mike_spada — Mon, 22 Mar 2021 08:35:40 GMT

Thank you, it works!

Mike