How to Display Distinct Order Numbers in Edit Path

SLam

Hello,

 

I am in need of assistance of how I could display only distinct order numbers while drilling down on a card. I am able to to get my summary number and chart to show me 4 distinct order numbers, however, when I drill down on the chart, it'll show me a total of 5 order numbers.

 

This is what the chart looks like:

ErrorDescription	Number of Errors
Missing Data	1
Illegible Copy	2
Invalid Data	1
Grand Total	4

 

When I drill down on one of the errors, and exit out of the drill down, it'll show me a total of 5 orders.

OrderNumber	ErrorDescription
1	Missing Data
1	Illegible Copy
2	Invalid Data
3	Missing Data
4	Missing Data

 

OrderNumber 1 is duplicated, and I understand it is because there are two separate error descriptions, but is there a way to only show 1 order number regardless of how many different error descriptions there are?

 

Ideal End Result:

OrderNumber	ErrorDescription
1	Missing Data
2	Invalid Data
3	Missing Data
4	Missing Data

GrantSmith

Hey @SLam 

 

What other columns do you have in your chart? Would it include the error message that isn't aggregated? It sounds like the grouping in your chart (non aggregated [count, min, max etc] fields) might be causing some issues as the summary number is looking across your entire dataset but depending on the fields you're including it might treat the values as non-distinct.

GrantSmith

Hi @SLam 

You could use a Min or Max function on the ErrorDecription field to display only one value on your drill path if you don't necessarily care about all of the different Error descriptions. That'd restrict it to a single value for each OrderNumber.

 

If you're interested in combining all the the error messages then you'd need to use a magic ETL to "combine strings separated by ," in a group by tile (grouping by order number) and then use that resulting dataset in your drill path card.

 

Unknown

Hey Grant,

Thanks for the input.

 

I am checking over another card and I am having some trouble with getting the numbers to show correctly.

 

For some reason, there are a total of 5 orders if you add up the distinct volumes, but the grand total and the summary number show 4. I know within the order numbers, there is a duplicate value in there, but I thought my beast mode would capture it.

Beast Mode: Distinct Volume = COUNT(DISTINCT `TENOrderNo`)

MarkSnodgrass

I would check if you have anything in the sorting properties. That can throw off Domo's ability to count distinct.

Unknown

Hey Mark, 

 

I have nothing in the sorting properties.

GrantSmith

Hey @SLam 

 

What other columns do you have in your chart? Would it include the error message that isn't aggregated? It sounds like the grouping in your chart (non aggregated [count, min, max etc] fields) might be causing some issues as the summary number is looking across your entire dataset but depending on the fields you're including it might treat the values as non-distinct.

Unknown

Hi Grant,

 

Yes I am using some other columns that are non-aggregated.. I am assuming this will cause the issue I am seeing.

jaeW_at_Onyx

@SLam 

sorry ... i don't understand what you're driving at.  

 

COUNT(DISTINCT() ) counts... the number of distinct values. 

so the number of unique order no is 4.

if you want to know 'how many errors there are, don't do a count (distinct) just do a count... that would give you a total of 5.

 

when you drill down, if you want to see distinct values, make sure you're drilling down to a card (not the raw data) that has your aggregation in place ...cnt(dstnct, order no.) grp by order no... or similar.