What NODISTINCT parameter does in AGGR function?

[#1]

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

[swuehl]

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

[christian77]

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.

[#4 · Accepted solution]

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

[vikasmahajan]

Very precisely  explained !!

Thanks

Vikas

[saray_JD]

Hi

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

[vajid4525]

Hi,

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