topic Re: Adding expression as column in QlikView

Adding expression as column

Tue, 05 May 2015 11:18:51 GMT

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

ramoncova06 — Tue, 05 May 2015 15:29:17 GMT

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

Tue, 05 May 2015 15:29:23 GMT

Can you post an example qvw?

Re: Adding expression as column

Tue, 05 May 2015 15:45:33 GMT

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

ReadingDate,=avg(Cost)

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

ramoncova06 — Tue, 05 May 2015 16:22:01 GMT

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

Tue, 05 May 2015 16:26:05 GMT

Check the file i attached. Hope it will helpful.

Re: Adding expression as column

Tue, 05 May 2015 16:27:04 GMT

ps. I add some records in your inline example statement.

Re: Adding expression as column

Tue, 05 May 2015 16:33:52 GMT

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?

If no, i'll download QlikView on another PC

Re: Adding expression as column

miikkaqlick — Tue, 05 May 2015 16:38:05 GMT

Hi!

From your application.

This should fix problem

=avg(TOTAL Cost)

Br,

Miikka

Climber Finland

Re: Adding expression as column

Tue, 05 May 2015 16:41:23 GMT

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

=num(Sum(Aggr(Sum({<flag_approxYr={1}>} Cost),ReadingDate)) / count(ReadingDate),'£#,###.##')

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

stevedark — Tue, 05 May 2015 23:42:01 GMT

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