How to get value, depending on unique value of other fields

[#1]

Hello all!

I have a problem which I have simplified below. I have the following fields in a data table:

SALES
ENTRY_ID

Due to the necesity to built a star sheme as data model, I needed to join this table with some others resulting to a final table which looks like this:

SALES
ENTRY_ID
EXTRA_FIELD

I need to sum the SALES but only for thoose records where the entry_ID is distinct. In my first data model I would have done:

sum(SALES)

Now I will do:

sum(aggr(only(SALES),ENTRY_ID))

(is that right?)

Now the problem is, I need to use this function in a chart with as dimension KPI_ID (a fourth value, independent on the first three values). To avoid the problem stated here (http://community.qlik.com/message/166081#166081), I will need to add 'nodistinct' to my expression:

sum(aggr(nodistinct only(SALES),ENTRY_ID))

However, now the result gives me the same as if I would use sum(SALES), all the SALES are included, even thoose that have the same ENTRY_ID!! How can I solve this problem?

Please also see the attached qvw.

Thank you very much!

[CELAMBARASAN]

Hi,

     Try with this

     sum(aggr(if(Count(ENTRY_ID)=1,Sum(SALES)),ENTRY_ID))

Celambarasan

[#3]

That just gives me a bunch of zeros 😕

[CELAMBARASAN]

Hi,

     You mean for all entry ID it gives zeros or for duplicate ID's?

Celambarasan

[#5]

In a chart with EXTRA_FIELD as dimension, the given expression results in 0's for every record in EXTRA_FIELD.

But I realize that I wasn't entirely correct in my simplified situation I described in my first post. Creating a chart with EXTRA_FIELD as dimension and this as expression:

sum(aggr(nodistinct only(SALES),ENTRY_ID))

works fine. The problem occurs when I have a fourth variable not linked to the previous three variables, lets call it KPI_ID. Making a chart with this field as dimension and as expression:

sum(aggr(nodistinct only(SALES),ENTRY_ID))

will result in incorrect somation as the complete SALES, even with nodistinct ENTRY_IDs, are taken into account. using:

sum(aggr(only(SALES),ENTRY_ID))

will result in only 1 calculated value...

I will update my first post in order to reflect the correct situation.

[#6 · Accepted solution]

Ok I found the anser to the problem!

Apparentely in order to avoid the use of NODISTINCT in the AGGR() function, you can involve the problematic dimension (in my case the KPI_ID) in the aggregation dimensions. So the correct expression to use is:

sum(aggr(nodistinct only(SALES),ENTRY_ID,KPI_ID))

simplifying a problem is the way to go when you've spent hours on puzzling out something complex

Thank you Celambarasan Adhimulam for your help!