new user with simple AR questions

user07997

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?

Unknown

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

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

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

Unknown

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

user07997

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

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

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

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

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