topic Re: What NODISTINCT parameter does in AGGR function? in QlikView

What NODISTINCT parameter does in AGGR function?

Tue, 26 Jun 2012 08:58:21 GMT

Hi,

I try to figure out what the NODISTINCT parameter does in the AGGR function. When do we need to use it?

Do you have some clear examaple for this?

Thanks,

Guy

Re: What NODISTINCT parameter does in AGGR function?

swuehl — Tue, 26 Jun 2012 09:16:53 GMT

Guy,

I assume you've read the description in the Help, so I am not going to copy it here.

Maybe this thread is of any help (if you follow the links to Eva's other posts, you see also some examples).

http://community.qlik.com/message/222026

Regards,

Stefan

edit:

This is the link to Evas thread including the sample file:

http://community.qlik.com/message/221791

Re: What NODISTINCT parameter does in AGGR function?

christian77 — Tue, 26 Jun 2012 09:18:46 GMT

Hi:

Sometimes aggr funcktion fills only some rows. If it does not work as expected, add nodistinct and it will.

If you started with nodistinct, and it doesn't work, take it away and see the result.

That's what I do.

The reason is that some rows have more than a single value and it retunrs null.

Re: What NODISTINCT parameter does in AGGR function?

Wed, 27 Jun 2012 09:13:06 GMT

Hi,

I did a little research on this issue, and that's what i found out:

Let assume the bellow data set:

Country	Month	Prod	Metric
IS	1	A1	40
IS	1	A2	50
IR	1	A3	30
IR	2	A1	10

I want to use a AGGR in order to consider the sum by Country and Month only, and let it be presented in a chart with Country, Month and Prod dimensions.

As you can see, the =AGGR(SUM(Metric),Country,Month) will not fill all the rows data, because the AGGR prepare a data set like this:

Country	Month	Metric
IS	1	90
IR	1	30
IR	2	10

and when QlikView try to use this set according to the Chart dimensions, it finds out it lacks information about the Prod Dimension, so it fills 90 only in the first row of the combination Country=IS and Month=1. The other row with the same combination gets Null ( - )

Country	Month	Prod	Simple SUM	=AGGR(SUM(Metric),Country,Month)	=AGGR(NODISTINCT SUM(Metric),Country,Month)
IS	1	A1	40	90	90
IS	1	A2	50	-	90
IR	1	A3	30	30	30
IR	2	A1	10	10	10

We can Solve it by adding NODISTINCT qualifier to the AGGR function, which causes the data to be replicated for the same combination (see above).

I hope it helps!

Guy

Re: What NODISTINCT parameter does in AGGR function?

vikasmahajan — Fri, 07 Oct 2022 06:35:10 GMT

Very precisely explained !!

Thanks

Vikas

Re: What NODISTINCT parameter does in AGGR function?

saray_JD — Sun, 30 Jul 2023 06:12:17 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

Re: What NODISTINCT parameter does in AGGR function?

vajid4525 — Sun, 17 Sep 2023 06:25:02 GMT

Hi,

=AGGR(SUM(Metric),Country,Month) with expression output is different for me.