10 Replies Latest reply: May 5, 2015 7:42 PM by Steve Dark

Hello. I've got chart at which i'm calculating values of a few dates. I'd like to have pivot table where at the top side there are values from one expression and the rest of expressions and dimentions to be at the left side of the table. Is it possible?

Second question - i want to make avg of a few fields. I've got column where i get sum of fields and from this sums i want avg. There are some values missing. Any ideas?

• ###### Re: Adding expression as column

1. yes you can do this, just position on top of the field you want to move and drag it to wherever you want

2. would have to see more details

• ###### Re: Adding expression as column

Can you post an example qvw?

• ###### Re: Adding expression as column

I'm not sure if it opens, so i'll post table here.

11/04/2011,£10.00

28/05/2011,£326.00

02/06/2011,£12.00

28/06/2011,£53.00

10/09/2011,£956.00

11/10/2011,£63.00

01/01/2012,£36.00

11/04/2012,£150.00

12/05/2012,£100.00

Table looks like this, i've got average for every value, but i'd like to have average value of all this values in one field.

I already coped with positioning column, but i've got quite big problem with this field.

When i try to add another column where i take avg/sum of elements, in best case it 'sums' value from only one row, so it re-writes values i already have.

Also - some of the values can be missing - i can put there 0 or 'NaN'. Is it possible to make avg from something like this?

• ###### Re: Adding expression as column

so you want an average of averages ? or you just want one single avg that shows for all the data that you are presenting ?

• ###### Re: Adding expression as column

Hi!

This should fix problem

=avg(TOTAL Cost)

Br,

Miikka

Climber Finland

• ###### Re: Adding expression as column

Check the file i attached. Hope it will helpful.

• ###### Re: Adding expression as column

I feel so stupid...

I can't open file because i've got personal edition and i already opened file too many times...

Sorry.

Is there any other solution to open it?

• ###### Re: Adding expression as column

You can test that expression to solve calculation of avg for pre-aggregated sums:

wich create a "virtual" table of sums for every ReadingDate and than perform avg of that sums

to understand the logic check the image below

• ###### Re: Adding expression as column

Creating your chart and setting it to be a pivot is the simplest way of getting dimensions along the top and down the side.  It can be a bit fiddly pulling the dimensions to the correct places on the pivot, but it can be done.

If, however, you want to have different expressions in different columns (e.g. 12 months, then an extra column for averages) this can get problematic.

You can achieve some things by using the dimensionality and secondarydimensionality functions, which allow you to do different calculations depending on whether you are in a cell in the middle of the pivot or in a total column.  This does not allow you to do everything though.

Often I find that the only way to do columns for each month then additional columns (eg. average and total columns) is to have a separate expression for each month, and then expressions for the extra columns also.  For the month columns Set Analysis is required to get the single month values.  The expressions for the extra columns can then either reference the results of the prior columns, or use set analysis again over a broader period.

Hope that helps.

Steve