15 Replies Latest reply: Jun 16, 2016 4:04 PM by Sunny Talwar

# Total does not add up while using Distinct count function

Notice the pivot below. The sum of the columns does not add up to the total. This is because I am using this function to count for the details Count( Distinct [field]) however when it comes to the totals, it creates a different combination of distinct numbers so it produces that.

How do i make the totals just the summation of the details above and to the right while at the same time, keep the details with the same formula as Distinct count?

• ###### Re: Total does not add up while using Distinct count function

I guess you could work with a rangsum() function in combination with the aggr() function.

I don't know what the dimension is you are using here for the rows, but lets call it yourDimension. Try this expression:

rangesum(aggr(count(distinct [field]), yourDimension))

You might be using multiple dimensions, so just add those. For example:

rangesum(aggr(count(distinct [field]), yourDimension, yourOtherDimension))

I don't have any test data to try this myself, so let us know if it worked.

• ###### Re: Total does not add up while using Distinct count function

Or May be this:

Sum(Aggr(Count(Distinct [field]), yourDimension/s))

• ###### Re: Total does not add up while using Distinct count function

Made a typo (edited it). Should have been rangesum instead of rangsum.

RangeSum - script and chart function ‒ Qlik Sense

• ###### Re: Total does not add up while using Distinct count function

I tried it and got zero.

• ###### Re: Total does not add up while using Distinct count function

Same with Sum(Aggr(Count(Distinct [field]), yourDimension/s))

• ###### Re: Total does not add up while using Distinct count function

Sum(Aggr(Count(Distinct [PSA_Approved_Date__c]), Month, Aging))

• ###### Re: Total does not add up while using Distinct count function

Still getting zero

• ###### Re: Total does not add up while using Distinct count function

Is Aging a field or a calculation?

• ###### Re: Total does not add up while using Distinct count function

Its a calculation. So is month.

Here is the Aging formula

If (([PSA_Submitted_Date__c]-[pse__Expense_Date__c]) = '', '',If (([PSA_Submitted_Date__c]-[pse__Expense_Date__c]) < 8 ,'1-7 Days',If (([PSA_Submitted_Date__c]-[pse__Expense_Date__c]) < 16, '8-15 Days',If (([PSA_Submitted_Date__c]-[pse__Expense_Date__c]) <22, '16-21 Days',If (([PSA_Submitted_Date__c]-[pse__Expense_Date__c]) < 32, '22-31 Days','32+ Days' ) ))))

Here is the Month formula

Date(MonthStart(PSA_Approved_Date__c), 'MMM-YY')

• ###### Re: Total does not add up while using Distinct count function

Can you try this:

Sum(Aggr(Count(Distinct [PSA_Approved_Date__c]), PSA_Approved_Date__c, PSA_Submitted_Date__c, pse__Expense_Date__c))

Alternatively, can you calculated these dimensions in the script itself? Might improve performance as well.

• ###### Re: Total does not add up while using Distinct count function

Ok, Now I am getting some numbers but they don't seem to be correct. I filtered on just Aug-15 and just for 32+ and it shows the number as 6. But there is only one row there.

• ###### Re: Total does not add up while using Distinct count function

That is the reason I suggest calculating the dimensions in the script

• ###### Re: Total does not add up while using Distinct count function

Based on the color of the text in your expression (black) "Aging" is not the correct name of the Dimension. Keep in mind the spelling is case sensitive.

You posted a formula to calculate the content. I suggest you move that huge IF-statement to your script and make it an actual field in your data model. After that, try again.

Side effect of calculating that field in your script is that it will improve performance.

• ###### Re: Total does not add up while using Distinct count function

I noticed the Aging color as well. But I checked it again. The spelling is exact. Not sure what I am doing wrong.

Also I don't know how to bring it from the editor level. Can you help?

This is what I have

LIB CONNECT TO 'Salesforce_BULK';

[pse__Expense__c]:

PSA_Approved_Date__c,

PSA_Submitted_Date__c,

pse__Expense_Report__c,

pse__Amount__c,

pse__exchange_rate_Incurred_currency__c;

SELECT  pse__Expense_Date__c,

PSA_Approved_Date__c,

PSA_Submitted_Date__c,

pse__Expense_Report__c,

pse__Amount__c,

pse__exchange_rate_Incurred_currency__c

FROM pse__Expense__c;