Beast mode - Age buckets / grouping

user066906
user066906 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:

Best Answers

  • MarkSnodgrass
    MarkSnodgrass 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. 

    (CASE
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN '0-6 Months'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN '6 months - 1 year'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN '1-2 years'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN '2-3 years '
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years'
ELSE 'Unassigned'
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.
  • user066906
    user066906 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

  • MarkSnodgrass
    MarkSnodgrass 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. 

    (CASE
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN '0-6 Months'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN '6 months - 1 year'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN '1-2 years'
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN '2-3 years '
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 'Greater than 3 years'
ELSE 'Unassigned'
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.
  • user066906
    user066906 Member

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

     

     

  • user066906
    user066906 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

  • MarkSnodgrass
    MarkSnodgrass 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:

    (CASE
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <180 THEN 1
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <365 THEN 2
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <729 THEN 3
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) <1094 THEN 4
WHEN DATEDIFF (CURRENT_DATE(),`Value1`) > 1095 THEN 5
ELSE 6
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.

Categories