Average as last column in Pivot

[mike_spada]

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

[Frank_Hartmann · Accepted solution]

Maybe like attached.

add an Inline table to the script:

Load * Inline [

dim

1

2

];

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)))

[mike_spada]

Thank you, it works!

Mike