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

Adding expression as column

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?

1 Solution

Accepted Solutions
Not applicable

Re: Adding expression as column

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

05-05-2015 18-39-23.jpg

10 Replies
ramoncova06
Valued Contributor III

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

Not applicable

Re: Adding expression as column

Can you post an example qvw?

Not applicable

Re: Adding expression as column

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?

ramoncova06
Valued Contributor III

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 ?

Not applicable

Re: Adding expression as column

Check the file i attached. Hope it will helpful.

Not applicable

Re: Adding expression as column

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

Not applicable

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?

If no, i'll download QlikView on another PC

miikkaqlick
Contributor II

Re: Adding expression as column

Hi!

From your application.

This should fix problem

=avg(TOTAL Cost)

Br,

Miikka

Climber Finland

Not applicable

Re: Adding expression as column

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

05-05-2015 18-39-23.jpg

Community Browser