Frequency Chart / Histogram

user000253
user000253 Member
in Charting

Hello,

I have a range of Velocities (ft/s) and I am trying to show how many jobs we have performed within this range of velocities.


I need to remove the decimal places and would like to round the numbers up. Is there a function to do that in beast mode or ETL?


Then I would like to create a card that shows number of jobs between 0-10, 10-20, 30-40 etc to the highest value of 368.7. In the histogram chart I can't seem to define the bin ranges, is there a work around?


Thanks

Tagged:

Best Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user000253

    In beast modes you can use the ROUND function to round a number to a specified number of decimal places.

    ROUND(`number`, 0)

    In this case 4.1 would go to 4 and 4.6 -> 5

    If you want 4.1 -> 5 then you can use the CEILING function instead which returns the next highest whole number. (For reference FLOOR is the opposite and will drop any trailing decimals)

    CEILING(`number`)


    With the histogram you're not able to define the bin values, it does that for you automatically.

    If you're wanting to do custom binning you can do a bar chart and define your bin value as a beast mode:

    CASE
  WHEN CEILING(`number`) <= 10 THEN '0-10'
  WHEN CEILING(`number`) <= 20 THEN ’10-20'
  WHEN CEILING(`number`) <= 30 THEN ’20-30'
  WHEN CEILING(`number`) <= 40 THEN ’30-40'
  WHEN CEILING(`number`) <= 50 THEN ’40-50'
  WHEN CEILING(`number`) <= 60 THEN ’50-60'
  WHEN CEILING(`number`) <= 70 THEN ’60-70'
  WHEN CEILING(`number`) <= 80 THEN ’70-80'
  WHEN CEILING(`number`) <= 90 THEN ’80-90'
...
  WHEN CEILING(`number`) <= 340 THEN ‘330-340'
  WHEN CEILING(`number`) <= 350 THEN ‘340-350'
  ELSE ‘350+’ END

    (You'll need to write the ... section. Copy and paste is your friend)


    You can then place that beast mode as your x axis and count your numbers to get the number in each bin.


    The beast mode works because it's exiting out after it finds the first condition that's true. So while 5 is less than 10 and 20 and all the other numbers it only returning 0-10 since that's the first condition that's true.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user000253 To handle the sorting, create a second beast mode with the exact same logic, but replace your when statement with numerical values. Drag this new beast mode into the sorting area and make sure aggregation is off and it is sorting ascending.

    **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.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user000253

    You'll need to define your custom sorting order as the bins are now being treated as strings and "30" comes after "280"'

    CASE
  WHEN CEILING(`number`) <= 10 THEN 1
  WHEN CEILING(`number`) <= 20 THEN 2
  WHEN CEILING(`number`) <= 30 THEN 3
  WHEN CEILING(`number`) <= 40 THEN 4
  WHEN CEILING(`number`) <= 50 THEN 5
  WHEN CEILING(`number`) <= 60 THEN 6
  WHEN CEILING(`number`) <= 70 THEN 7
  WHEN CEILING(`number`) <= 80 THEN 8
  WHEN CEILING(`number`) <= 90 THEN 9
...
  WHEN CEILING(`number`) <= 340 THEN 34
  WHEN CEILING(`number`) <= 350 THEN 35
  ELSE ‘350+’ END


    Alternatively you can utilize a simpler formula and math to get a sort order since you want a bucket size of 10:

    FLOOR(CEILING(`number`) / 10)


    Then put this value into your sorting on your card.

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user000253

    In beast modes you can use the ROUND function to round a number to a specified number of decimal places.

    ROUND(`number`, 0)

    In this case 4.1 would go to 4 and 4.6 -> 5

    If you want 4.1 -> 5 then you can use the CEILING function instead which returns the next highest whole number. (For reference FLOOR is the opposite and will drop any trailing decimals)

    CEILING(`number`)


    With the histogram you're not able to define the bin values, it does that for you automatically.

    If you're wanting to do custom binning you can do a bar chart and define your bin value as a beast mode:

    CASE
  WHEN CEILING(`number`) <= 10 THEN '0-10'
  WHEN CEILING(`number`) <= 20 THEN ’10-20'
  WHEN CEILING(`number`) <= 30 THEN ’20-30'
  WHEN CEILING(`number`) <= 40 THEN ’30-40'
  WHEN CEILING(`number`) <= 50 THEN ’40-50'
  WHEN CEILING(`number`) <= 60 THEN ’50-60'
  WHEN CEILING(`number`) <= 70 THEN ’60-70'
  WHEN CEILING(`number`) <= 80 THEN ’70-80'
  WHEN CEILING(`number`) <= 90 THEN ’80-90'
...
  WHEN CEILING(`number`) <= 340 THEN ‘330-340'
  WHEN CEILING(`number`) <= 350 THEN ‘340-350'
  ELSE ‘350+’ END

    (You'll need to write the ... section. Copy and paste is your friend)


    You can then place that beast mode as your x axis and count your numbers to get the number in each bin.


    The beast mode works because it's exiting out after it finds the first condition that's true. So while 5 is less than 10 and 20 and all the other numbers it only returning 0-10 since that's the first condition that's true.

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

    Hi @GrantSmith thank you very much. I have managed to do the custom bins in beast mode. The only thing now is I can't get the sorting fixed. It's taking 100-110 group etc before 20-30. Any ideas on how to fix this?


    Thanks, your help is much appreciated.

    image.png


  • user000253
    user000253 Member

    Hi @GrantSmith thank you very much. I have managed to do the custom bins in beast mode. The only thing now is I can't get the sorting fixed. It's taking 100-110 group etc before 20-30. Any ideas on how to fix this?


    Thanks, your help is much appreciated.

    image.png


  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @user000253 To handle the sorting, create a second beast mode with the exact same logic, but replace your when statement with numerical values. Drag this new beast mode into the sorting area and make sure aggregation is off and it is sorting ascending.

    **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.
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user000253

    You'll need to define your custom sorting order as the bins are now being treated as strings and "30" comes after "280"'

    CASE
  WHEN CEILING(`number`) <= 10 THEN 1
  WHEN CEILING(`number`) <= 20 THEN 2
  WHEN CEILING(`number`) <= 30 THEN 3
  WHEN CEILING(`number`) <= 40 THEN 4
  WHEN CEILING(`number`) <= 50 THEN 5
  WHEN CEILING(`number`) <= 60 THEN 6
  WHEN CEILING(`number`) <= 70 THEN 7
  WHEN CEILING(`number`) <= 80 THEN 8
  WHEN CEILING(`number`) <= 90 THEN 9
...
  WHEN CEILING(`number`) <= 340 THEN 34
  WHEN CEILING(`number`) <= 350 THEN 35
  ELSE ‘350+’ END


    Alternatively you can utilize a simpler formula and math to get a sort order since you want a bucket size of 10:

    FLOOR(CEILING(`number`) / 10)


    Then put this value into your sorting on your card.

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

Categories