Beast Mode for Aging Bucket

Hiraayub · 2021-10-22T16:14:27+00:00

There was an error rendering this rich post.

Hi, I'm writing a beast mode to create the aging bucket, it's is showing results but it's has everything under < 15 days. Note sure what I'm doing wrong here.

The 0.06% in 15-30 Days are the negative #

(CASE WHEN ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days'

WHEN ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days'

WHEN `Application Started` is NULL THEN 'App Not Submitted' END)

Find more posts tagged with

Accepted answers

GrantSmith

Your days are negative because your date assigned is coming after the application started date. And because -27 is less than 15 they're all falling into the bucket. You have a few options. One is you can utilize the ABS function to convert any negatives to positive so in you cases where the difference is -27 it'll evaluate to 27 and fill the bucket.

(CASE WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days'

 WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days'

 WHEN `Application Started` is NULL THEN 'App Not Submitted' END)

All comments

GrantSmith

Hi @Hiraayub

This is because you have your dates swapped. Which date is supposed to come first? You need to do your end date - your start date since the end date will be greater.

Swap `Application Started` with `Date Assigned` such that `Date Assigned` is coming first.

Hiraayub

Grant, our Data is very complicated at the moment there are a lot of manual date entries.

Date Assigned is when we assigned that case to someone to start the application so Date assigned first is right.

<15 days has all the correct data but it not creating the aging bucket < 15 and 15-30.

GrantSmith

(CASE WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 15 THEN '15 Days'

 WHEN ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) > 15 AND ABS((DATEDIFF(CURRENT_DATE(), `Application Started`)) - (DATEDIFF(CURRENT_DATE(), `Date Assigned`))) <= 30 THEN '15-30 Days'

 WHEN `Application Started` is NULL THEN 'App Not Submitted' END)

Hiraayub

thank you it worked :)