Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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?

Untitled.png

14 Replies
oknotsen
Honored Contributor III

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.

May you live in interesting times!
MVP
MVP

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

Or May be this:

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

oknotsen
Honored Contributor III

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

May you live in interesting times!
Not applicable

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

I tried it and got zero.

Please see below

2.png

3.png

Not applicable

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

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

MVP
MVP

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

You did not add Aging in your expression, try this:

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

Not applicable

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

Still getting zero

2.png

3.png

MVP
MVP

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

Is Aging a field or a calculation?

Not applicable

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')