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?
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 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)
Thanks! I understand that what this did was identify the status of the invoices by due date and color accordingly. Works perfectly!
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.
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
