Using Date in Face or Shape Chart Type

giannzi
giannzi Member
in Magic ETL

Hi, I want to create a chart where, if a report is unfinished and the due date is more than 3 weeks away from the current date, it appears green. If the due date is exactly 3 weeks from the current date, it appears orange. If the due date has already passed, it appears red. However, I can only input numbers within a range, not actual dates. Do you know how I can accomplish this? Thanks!

Tagged:

Answers

  • ggenovese
    ggenovese Coach

    You can use DATEDIFF to count the number of days and then use the range options in the card configuration something like this:

    MIN(CASE WHEN `Report Status` = 'unfinished' THEN DATEDIFF(CURDATE(),`Due Date`) END)

  • giannzi
    giannzi Member

    Hello @ggenovese, thank you. When you said 'card configuration,' were you referring to this chart property? Because it only accepts numbers.

    image.png
  • ggenovese
    ggenovese Coach
    edited March 14

    Hi @giannzi - DATEDIFF() returns the number of days between two dates.

    Example: MIN(DATEDIFF(`Due Date`, CURRENT_DATE()))

    If today is 03/13/2025 and the due date is 04/04/2025 it will return 21

    You can then use your ranges to provide the color rules. For example, the face card only allows 3 ranges and you don't have to provide a MAX for Green :

    image.png
    image.png
    image.png

    hope that helps!

Categories