Beast Mode for Aging Bucket

Hiraayub
Hiraayub Member
in Magic ETL

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)


Aging.PNG
Aging Negative.PNG


Best Answer

  • GrantSmith
    GrantSmith Coach
    edited October 2021 Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hiraayub
    Hiraayub Member

    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.


    Aging 1.PNG


  • GrantSmith
    GrantSmith Coach
    edited October 2021 Answer ✓

    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)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Hiraayub
    Hiraayub Member

    thank you it worked :)

Categories