Magic ETL

Magic ETL

new user with simple AR questions

Card #1 - my output shows total AR by customer. I would like it to show how much is current, 30 days, etc

 

Card #2 - my output shows AR due by day. How do I get all past due accounts to show as due today?

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answers

  • Unknown
    Answer ✓

    You might be able to acheive what you are looking for by creating a beast mode and by using something similar to the following:

     

    Beast mode name: AR Status

    case when DATEDIFF(CURRENT_DATE(),`Due Date Field`)<=30 then '(0-30 days)'
    when DATEDIFF(CURRENT_DATE(),`Due Date Field`)>30 AND DATEDIFF(CURRENT_DATE(),`Due Date Field`)<=60 then '(30-60 days)'

    when DATEDIFF(CURRENT_DATE(),`Due Date Field`)>60 AND DATEDIFF(CURRENT_DATE(),`Due Date Field`)<=90 then 'Past Due (60-90 days)'

    when DATEDIFF(CURRENT_DATE(),`Due Date Field`)>90 then 'Past Due (90+ days)'
    end

     

    For your horizontal stacked bar or grouped bar, you can insert this as your series and then set color rules as the following:

    AR Status = (0-30 days)  (Green)

    AR Status = (30-60 days) (light green)

    and so on...

     

     

  • Member
    Answer ✓

    For Card #2, I changed the formula a little bit but wouldn't have found this without your help - thanks

     

    (CASE when CURRENT_DATE()>`DueDate` then CURRENT_DATE() else `DueDate` end)

  • Member
    Answer ✓

    Thanks! I understand that what this did was identify the status of the invoices by due date and color accordingly. Works perfectly!

Answers

  • For card 1, what is your desired output chart type?

  • horizontal bar graph. I would like to see the bar show current as green, 30 day as light green, 60 day yellow, 90+ as red.

  • Unknown
    Answer ✓

    You might be able to acheive what you are looking for by creating a beast mode and by using something similar to the following:

     

    Beast mode name: AR Status

    case when DATEDIFF(CURRENT_DATE(),`Due Date Field`)<=30 then '(0-30 days)'
    when DATEDIFF(CURRENT_DATE(),`Due Date Field`)>30 AND DATEDIFF(CURRENT_DATE(),`Due Date Field`)<=60 then '(30-60 days)'

    when DATEDIFF(CURRENT_DATE(),`Due Date Field`)>60 AND DATEDIFF(CURRENT_DATE(),`Due Date Field`)<=90 then 'Past Due (60-90 days)'

    when DATEDIFF(CURRENT_DATE(),`Due Date Field`)>90 then 'Past Due (90+ days)'
    end

     

    For your horizontal stacked bar or grouped bar, you can insert this as your series and then set color rules as the following:

    AR Status = (0-30 days)  (Green)

    AR Status = (30-60 days) (light green)

    and so on...

     

     

  • For your second card, you apply the same type of beast mode logic:

     

    Beast Mode Name: Past Due Trigger

    CASE WHEN DATEDIFF(CURRENT_DATE(),`Due Date Field`)>30 then 1 end

     

    Then make Past Due Trigger a filter and set the filter equal to 1

     

     

  • Member
    Answer ✓

    For Card #2, I changed the formula a little bit but wouldn't have found this without your help - thanks

     

    (CASE when CURRENT_DATE()>`DueDate` then CURRENT_DATE() else `DueDate` end)

  • Member
    Answer ✓

    Thanks! I understand that what this did was identify the status of the invoices by due date and color accordingly. Works perfectly!

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In