Sum for distinct

[KC3]

Dear Qlikians,

Having issue for getting Total for Distinct count.

I applied formula -Count(DISTINCT{<[Sale Order Status New]={'DONE'}>}[Display Order Code])

As need only done data in it please help me to get the sum/ total for this.

PFB image for this

Thanks in advance

[rubenmarin]

Hi, the count is less than the sum of columns because there are [Display Order Code] in more than one Facility_Updated, so them counts in more than one column but just once in the totals.

To get the same sum you can use:

- Count(DISTINCT{<[Sale Order Status New]={'DONE'}>}[Display Order Code]&Facility_Updated)

- Sum(Aggr(Count(DISTINCT{<[Sale Order Status New]={'DONE'}>}[Display Order Code]),Facility_Updated))

But note that, if the same [Display Order Code] you get for different facilities is the same order, the total current  count you get is correct. If they are differnt and just have the coincidence to have the same number then you can use any of the above or create a composite key in script with [Display Order Code]&'_'&Facility_Updated and use the count distintc of this field.

[KC3]

Hi Rube,

Thanks for that

Now the total is coming correct but in the table different status are coming how to remove it.

PFB image for you refrence.

[rubenmarin · Accepted solution]

Hi, you can try this one:

- Sum({<[Sale Order Status New]={'DONE'}>}Aggr(Count(DISTINCT{<[Sale Order Status New]={'DONE'}>}[Display Order Code]),Facility_Updated))

If the previous doesn't works:

- Sum({<[Sale Order Status New]={'DONE'}>}Aggr({<[Sale Order Status New]={'DONE'}>}Count(DISTINCT{<[Sale Order Status New]={'DONE'}>}[Display Order Code]),Facility_Updated))

Or use the first option: Count(DISTINCT{<[Sale Order Status New]={'DONE'}>}[Display Order Code]&'_'&Facility_Updated)

[KC3 · Accepted solution]

Thanks for the quick response it's really helpful Rubenmarin.

KC