Aggr

[#1]

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

[swuehl · Accepted solution]

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

[#3]

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

[danielrozental]

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

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

[IAMDV]

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,

DV

[swuehl]

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

[IAMDV]

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,

DV

[swuehl]

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

[IAMDV]

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,

DV

[#10]

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) ?