new user with simple AR questions

user07997
user07997 Member
in Magic ETL

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?

Best Answers

  • Unknown
    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...

     

     

  • user07997
    user07997 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)

  • user07997
    user07997 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

  • Unknown
    Unknown

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

  • user07997
    user07997 Member

    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
    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...

     

     

  • Unknown
    Unknown

    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

     

     

  • user07997
    user07997 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)

  • user07997
    user07997 Member
    Answer ✓

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

Categories