Weird Sorting Issue - Total Sales

imelendez
imelendez Member
in Charting

Hi, Experts, 

 

I created a domo card in table view for some top-level KPIs. I am running into an issue when I sort by Total Sales (calculated field/beast mode) by ascending you get the column sorted by 0 to the bottom. When you sort by descending it sorts by blanks.

 

Thoughts or help would be greatly appreciated.

Comments

  • GrantSmith
    GrantSmith Coach

    That's functioning as intended. NULLs always appear last. The actual data should appear after the nulls decending. What sort of behavior are you expecting?

     

    If you want to override the sorting behavior you can utilize a separate beast mode to handle the nulls differently and sort based on this new field rather than the actual data field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • imelendez
    imelendez Member

    @GrantSmith I have never attempted to do that. How would you do that? Can you provide an example how you would do a beast mode for the nulls as you mentioned?

  • ST_Superman
    ST_Superman Domo Employee

    You can update your calculated field to something like this:

     

    case 
       when `Total Sales` is null then 0 
       else `Total Sales`
    end

    If you can share your `Total Sales` beast mode, I could give you a more concrete example

  • GrantSmith
    GrantSmith Coach

    @imelendez - 

     

    @ST_-Superman-_ has it correct. It's a simple case statement to handle the nulls and replace it with 0.

     

    Depending on your aggregate level you may need to wrap it in a SUM() (or do this on the sorting section of the card building interface)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • imelendez
    imelendez Member

    Here is the code - sorry for the delay.

     

    SUM(CASE 
    WHEN `rpt_job.authorized` = 1 
    THEN (`rpt_job.subtotal_sales`) / 100
    END)

    Thanks for your help in advance @ST_-Superman-_ 

  • GrantSmith
    GrantSmith Coach

    @imelendez 

     

    You should just need a simple tweak to your case statement to include an ELSE clause:

     

    SUM(CASE 
    WHEN `rpt_job.authorized` = 1 
    THEN (`rpt_job.subtotal_sales`) / 100
    ELSE 0
    END)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • DataMaven
    DataMaven Coach

    @imelendez ... Didn't I harp about null values in our work together?  LOL

    Domo does not handle nulls well.  Get rid of them in your ETL before the card.  Beast mode is fine in the interim, but you know I always want it firmly built in ETL if it can be.  

    DataMaven
    Breaking Down Silos - Building Bridges
    **Say "Thanks" by clicking a reaction 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.

Categories