Skip to content

    • Charting

    Charting

    Beast mode - Age buckets / grouping

    Member
    in Charting

    Hello everyone,

     

    I am having trouble with a beast mode grouping outstanding records by age bucket. I am looking to calculate the number of days a record has open and then place the record within a range. Below you will find my existing beast mode which is an "Invalid formula". If someone could assist with the formula validation or suggest enhancements it would be greatly appreciated! I have reviewed many support articles related to my issue and I cannot seem to find a solution. 

     

    (CASE

    WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years'
    WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 730 AND <1094 THEN '2-3 years '
    WHEN DATEDIFF (CURRENT_DATE(),`Value1`) >365 AND <729 THEN '1-2 years'

    WHEN DATEDIFF (CURRENT_DATE(),`Value1`) >181 AND <365 THEN 6 months - 1 year'

    WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 0 AND <180 THEN '0-6 Months'
    WHEN `Value1` IS NULL THEN 'Unassigned'
    END)

    Tagged:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Coach
      Answer ✓

      The issue is your AND statement in each sequence. You would need to re-state the DATEDIFF formula after the AND again. 

      You might consider re-writing it in reverse order and it would eliminate the need to have the AND statement because CASE statements exit after finding a match to the condition. 

      1. (CASE
      2. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN '0-6 Months'
      3. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN '6 months - 1 year'
      4. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN '1-2 years'
      5. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN '2-3 years '
      6. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years'
      7. ELSE 'Unassigned'
      8. END)

       Hope this helps. 

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

      I am now having trouble sorting the series using the above beast mode. I wrote the beast mode below and added the field to the sort section. The data is not sorting properly, I am set to no aggregation and I have also tried ascending and descending options. I do not see anything in properties that would be preventing the sort.

       

      Does anyone have any thoughts as to how I can sort this data series? 

       

      CASE
      WHEN `Value1` = '0-6 Months' THEN 1
      WHEN `Value1` = '6 months - 1 year' THEN 2
      WHEN `Value1` = '1-2 years' THEN 3
      WHEN `Value1` = '2-3 years' THEN 4
      WHEN `Value1` = 'Greater than 3 years' THEN 5
      ELSE 10
      END

    Answers

    • Coach
      Answer ✓

      The issue is your AND statement in each sequence. You would need to re-state the DATEDIFF formula after the AND again. 

      You might consider re-writing it in reverse order and it would eliminate the need to have the AND statement because CASE statements exit after finding a match to the condition. 

      1. (CASE
      2. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN '0-6 Months'
      3. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN '6 months - 1 year'
      4. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN '1-2 years'
      5. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN '2-3 years '
      6. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years'
      7. ELSE 'Unassigned'
      8. END)

       Hope this helps. 

      **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.
    • Member

      It looks like the suggested solution will solve my problem. Thank you for the suggestion.

       

       

    • Member
      Answer ✓

      I am now having trouble sorting the series using the above beast mode. I wrote the beast mode below and added the field to the sort section. The data is not sorting properly, I am set to no aggregation and I have also tried ascending and descending options. I do not see anything in properties that would be preventing the sort.

       

      Does anyone have any thoughts as to how I can sort this data series? 

       

      CASE
      WHEN `Value1` = '0-6 Months' THEN 1
      WHEN `Value1` = '6 months - 1 year' THEN 2
      WHEN `Value1` = '1-2 years' THEN 3
      WHEN `Value1` = '2-3 years' THEN 4
      WHEN `Value1` = 'Greater than 3 years' THEN 5
      ELSE 10
      END

    • Coach

      You need to use your DATEDIFF evaluation in your case statement and not value1 since that is just a date. It would look like this:

      1. (CASE
      2. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN 1
      3. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN 2
      4. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN 3
      5. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN 4
      6. WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 5
      7. ELSE 6
      8. END)

      Drag this beast mode function into your sort and then you should see your card display correctly. 

      **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.
    This discussion has been closed.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories