Date Range Buckets

Options
user009052
user009052 Member
in Magic ETL

Hello,

 

I am trying to create a way to look at upcoming estimated shipping dates but I keep getting an "error during processing" .

 

I have made buckets for WIP based off days in shop field and basic case formulas in the past with no issues (See attached).

 

I think I am having issues with my approach due to this being dates instead of a raw value.

 

In theory: 

case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’ else

case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’ else

case when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’ else

case when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days)  then ‘next 21 days’ else

'Over 21 days' end end end end

 

 

Tagged:

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    your syntax for a case statement is wrong.

     

    case

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’ 

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’ 

    when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days)  then ‘next 21 days’

    else 'Over 21 days'

    end

     

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    your syntax for a case statement is wrong.

     

    case

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 3 days) then ‘next 3 days’ 

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 7 days) then ‘next 7 days’ 

    when `est_ship_date’ < DATE_ADD(CURRENT_DATE()interval 14 days) then ‘next 14 days’

    when `est_ship_date` < DATE_ADD(CURRENT_DATE()interval 21 days)  then ‘next 21 days’

    else 'Over 21 days'

    end

     

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • JasonAltenburg
    JasonAltenburg Coach

    I think you are missing a comma in the DATE_ADD statement. Try changing

    DATE_ADD(CURRENT_DATE()interval 3 days)

    to:

    DATE_ADD(CURRENT_DATE(), interval 3 days)

     

    Also I'm not sure why you have so many case/end statements?  I use the following block of code for date buckets, and it works fine else-chaining without the Else/Case between, thought you might want to see to possibly shorten your code.

     

    Case
when DATEDIFF(`Event Date`,`Boxed Date`) < 0 then 'Prior to Box Date'
when DATEDIFF(`Event Date`,`Boxed Date`) < 91 then 'Under 3 Months'
when DATEDIFF(`Event Date`,`Boxed Date`) < 181 then '3-6 Months'
when DATEDIFF(`Event Date`,`Boxed Date`) < 271 then '6-9 Months'
when DATEDIFF(`Event Date`,`Boxed Date`) < 366 then '9-12 Months'
when DATEDIFF(`Event Date`,`Boxed Date`) > 365 then 'Over 12 Months'
when `Event Date`is null then 'Production'
else 'Unknown Timeframe Error'

end

     

Categories