topic Using a calculated dimension in Aggr() in QlikView

Using a calculated dimension in Aggr()

Tue, 27 May 2014 10:30:29 GMT

Hello fellow qlikviewers,

I was wondering about wether it was possible to use a calculated dimension to aggregate by using the Aggr() function.

I have a dataset where data is logged every second, which I would like to, for example, aggregate per 15 minutes.

I can make a straight table chart using

Floor(Sec,900)

as a dimension. This would however not enable me to take the average of each result.

I tried using the following (example):

Avg(Aggr(Count(Something),Floor(Sec,900)))

But it seems the Aggr() function won't accept this (getting no results).

I've also attached a quick example

What I can do is create a new field during the load script the create the value which I wish to aggregate by (Floor(900) in above examples), but I would prefer not to have to do this.

Any help will be greatly appreciated!

Kind regards,

Daniel

Re: Using a calculated dimension in Aggr()

tresB — Tue, 27 May 2014 10:35:40 GMT

Yes, aggr() would not respond to calculated dimension. Unfortunately, you have to create it in the script.

Re: Using a calculated dimension in Aggr()

Tue, 27 May 2014 13:15:33 GMT

Hi,

As Treseco told, you cant have coalc dim in aggr. But in the case from your example expression:

sum(d2)/count(DISTINCT Aggr(Floor(d1,2),d1))

should give expected result. You may need maybe one more if to check cases that there is no d2 for some d1.

Maybe in you real case you may use similiar....

regards

Darek