Beast mode - Age buckets / grouping

user066906

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)

MarkSnodgrass

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

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

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

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

 

 

user066906

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

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.