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



Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • 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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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`)

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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hey Mark, 

     

    I have nothing in the sorting properties.

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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 Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hi Grant,

     

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

  • @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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.