Beast mode - Age buckets / grouping

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)

Best Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    Accepted 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. 

  • user066906
    user066906 ⚪️
    Accepted 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

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

     

     

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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. 

This discussion has been closed.