How to Display Distinct Order Numbers in Edit Path

Reply
Yellow Belt

How to Display Distinct Order Numbers in Edit Path

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:

ErrorDescriptionNumber of Errors
Missing Data1
Illegible Copy2
Invalid Data1
Grand Total4

 

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.

OrderNumberErrorDescription
1Missing Data
1Illegible Copy
2Invalid Data
3Missing Data
4Missing 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:

OrderNumberErrorDescription
1Missing Data
2Invalid Data
3Missing Data
4Missing Data




Accepted Solutions
Black Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post


All Replies
Black Belt

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.

 



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Yellow Belt

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

Black Belt

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Yellow Belt

Hey Mark, 

 

I have nothing in the sorting properties.

Black Belt

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.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**

View solution in original post

Yellow Belt

Hi Grant,

 

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

Black Belt

@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.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
New & Improved Dojo: Get excited for a new feel and additional features in the online community.

Site will be down February 4-5th for maintenance.

Launch coming February 8th!