Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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!
Hi,
Try with this
sum(aggr(if(Count(ENTRY_ID)=1,Sum(SALES)),ENTRY_ID))
Celambarasan
That just gives me a bunch of zeros 😕
Hi,
You mean for all entry ID it gives zeros or for duplicate ID's?
Celambarasan
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.
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!