topic Re: Aggr in QlikView

Aggr

Tue, 22 May 2012 18:00:56 GMT

hi,

I did a formula =aggr(sum(Metric),Country, Month) and I would like display it by product.

do you know why, it's working for the first product, but I cannot repeat values for all of them?

thanks,

Eva

Aggr

swuehl — Tue, 22 May 2012 18:19:55 GMT

Eva,

could you describe your data model a bit more, best by uploading a small sample?

I am not completely sure about your setting, but have you tried adding the NODISTINCT qualifier to the aggr() function?

=aggr( NODISTINCT sum(Metric),Country, Month)

Regards,

Stefan

Aggr

Tue, 22 May 2012 18:22:49 GMT

No disctinct works perfectly !!! thanks a lot. I looks like a magician !

Aggr

danielrozental — Tue, 22 May 2012 18:23:01 GMT

If you need product, you probably have to add it to the dimensions

=aggr(sum(Metric),Country, Month, Product)

Re: Aggr

IAMDV — Wed, 23 May 2012 10:46:58 GMT

swuehl / Stefan,

I'm very curious to understand on how did you predict/guess that missing NoDistinct qualifier was the issue in the above expression and without even looking at the dataset? Please can you explain more about NoDistinct Qualifier using in conjuction with Aggr()?

Thanks in advance and I'll look forward to hear from you.

Cheers,

Re: Aggr

swuehl — Wed, 23 May 2012 11:25:57 GMT

Whenever I read something like

>..it's working for the first product, but I cannot repeat values for all of them...

That's the symptom if aggr() is used just with it's implicite DISTINCT qualifier on multiple occurences of identical dimension combinations (talking about the aggr() dimensions here). I just guessed that this was the case in Eva's setting (and indeed, in her other thread I discovered shortly after, you can see the data and the context of the function in her table).

I still found it a bit strange that Qliktech uses an implicite DISTINCT qualifier with aggr(), but not with count() etc, which I think causes a lot of confusion for the users (ahm, I don't want to suggest a default DISTINCT qualifier for count(), just pointing the difference!). In fact, I think this type of issue is among the Top5 I have answered in this forum.

Instead of using the NODISTINCT, you can most of the time work with a TOTAL<FIELDS> within an aggregation, I think

=aggr( sum(total<Country,Month> Metric), Country, Month, Product)

will be equivalent in Eva's setting. But I would assume that NODISTINCT should be more performant for QV to calculate (and also a bit shorter to read).

Regards,

Stefan

Re: Aggr

IAMDV — Wed, 23 May 2012 15:44:09 GMT

Stefan,

Excellent stuff and it totally makes sense. Thank you very much for detailed explanation.

I would expect Concat() function to behave as Aggr() function. I mean, I would like to think that Concat() function should return distinct values by default unless we use NODISTINCT qualifier. Again, I might be wrong but that's just my understanding.

And I understand that FirstSortedValue() function allows us to use DISTINCT qualifier but it has no meaning. I'm not sure using if there is performance again by using DISTINCT qualifier in FirstSortedValue() function?

Anyway, I'll get used to all this stuff and learn more. But I really appreciate all your help and time. Please let me know whenever you are in London. I'd love to catch up.

Cheers,

Re: Aggr

swuehl — Wed, 23 May 2012 16:26:10 GMT

Yes, I think I agree with the concat() function. Default DISTINCT qualifier would be appropriate, I believe.

With the FirstSortedValue(), I believe there is in fact a setting where the DISTINCT is applicable:

When your sort weight [btw, the Helps says it's optional, but I can't get it see a use for FirstSortedValue without giving the sort argument?] shows duplicate values at the requested sort order, FirstSortedValue() will by default return NULL because the answer is ambiguous, right?

Using DISTINCT, you can force QV to return a value, it will take the value with the lowest load order.

Sort, Value

1, 1

1, 2

2, 3

=FirstSortedValue( Value, Sort) will return NULL

=FirstSortedValue( distinct Value, Sort) will return 1

Ok, but I have another nice one: What is the use for DISTINCT in only() ?

I am referring to the HELP version I am using here (QV11, english).

Have a nice day,

Stefan

Re: Aggr

IAMDV — Thu, 24 May 2012 14:49:04 GMT

Stefan - This is awesome explanation. I like your clarity of thought. I always used to wonder - how to use FirstSortedValue() with duplicate values. This makes complete sense and you are right, QlikView Reference Guide states that Sort Weight is optional but it doesn't work without Sort Weight field.

I agree with you on Only() function which is classic example which accepts Distinct qualifier without any practical use.

Thanks again for your time and detailed explanation.

Have a good day.

Cheers,

Re: Aggr

Mon, 11 Jun 2012 17:53:28 GMT

Couldn't this situation have also been solved by using the product name as the earliest dimesion, i.e. =aggr(sum(Metric), Product, Country, Month) ?

Re: Aggr

Anonymous — Thu, 29 Oct 2015 11:42:20 GMT

Great discussion! I am facing the same problem.

When using =aggr( NODISTINCT sum(Metric),Dim1, Dim2) how to display the total in a straight table?

When using Expression Total it will give us the sum of the nodistinct what is wrong.

So how can it be achieved to get the aggregation values repeated (nodistinct) inside the table, and a (distinct) total sum above?

Re: Aggr

saray_JD — Sun, 30 Jul 2023 06:10:59 GMT

you can use Total <Group by Fields> in sum function instead of Aggr too.

like this :

=SUM(Total<Country,Month> Metric)

Have good Day

Saray