Count Distinct Number Changes

SamEich
SamEich Contributor
in Magic ETL

I've run into a really weird problem, that I'm curious if any one has ever seen it:

 

I have a beast mode that counts the number of orders based on their unique identifier. The formula is:

Count(Distinct `Name`).

 

I have a combo bar/line graph that shows number of orders & revenue by month. I noticed that the order count was really high (See "Incorrect Number of Orders (1).png"). Showing 442 for March. When I exported the underlying data from the same data set, I noticed that the actual number of distinct "Names" was 336 for March.

 

I then duplicated the card and added in another column that counts the distinct number of lines for the month in a bar next to the number of orders. (a "line" represents a item/order combo. You can have multiple lines/order). That is the ONLY thing I changed between the 2 graphs, and my Count(Distinct `Name`) function changed values to the correct 336 number for March (see screenshot "Number of Orders Correct (1).png"). The other card without the 2nd bar is still showing the incorrect number of orders

 

So my count distinct formula changes based on the other columns that are in the bar? Makes no sense to me. I've opened a case with domo support, but am curious if any users have experienced this before.

Best Answer

  • SamEich
    SamEich Contributor
    Answer ✓

    Ok, Domo engineering had to get involved, and they deployed a fix that seems to have worked. I can now take the trim() statement out of all my count distincts. Thanks for they support everyone!

Answers

  • Chips
    Chips Domo Employee

    @rhollander Didn't we run into this on the Rep Scorecard? Had something to do with the "hide date on card" selection, maybe? 

    Domo Consultant

    **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"
  • SamEich
    SamEich Contributor

    Still no solution yet! I wanted to add a video showing how finicky the count distinct has been as of late for us.

    here is a grouped & stacked bar. The bar on the left is total number of orders (Count(Distinct 'Order Name')), and the bars on the rights are beast modes that add up to the total number of orders (number of orders shipped on time/early, number of orders shipped late, and number of orders not shipped yet). See how when I have the date range set to last 5 months the two bars equal each other for march. Then when I change it to last 6 months, the "Total Orders" count distinct goes all haywire... Hopefully domo support will get back to me soon with a fix....

    Video:

    https://drive.google.com/file/d/1HlCDug1bgcebcHv4c0T9UjA9au3T2QbM/view?usp=sharing

  • rahul93
    rahul93 Contributor

    @SamEich Hi, 

    Since it is a beast mode which does its calculation during run-time which is why it takes the other columns in the chart into account. You could use another field to put in your number of distinct orders and then use it in the chart.

  • SamEich
    SamEich Contributor

    Heard back from Domo Support-- They haven't pinpointed the root cause yet, but they did find that if a Trim() function around the "Name" field will fix the issue. This is strange because there doesn't seem to be additional spaces or characters on the end of the fields when we export the data.

    More updates coming when available. 

  • SamEich
    SamEich Contributor
    Answer ✓

    Ok, Domo engineering had to get involved, and they deployed a fix that seems to have worked. I can now take the trim() statement out of all my count distincts. Thanks for they support everyone!

  • [Deleted User]
    [Deleted User] Member

    @SamEich

     

    Thanks for following up on this!

    Regards,

    Dani

Categories