Can I change the color on a running total line for future months?

learningdomo
learningdomo Member
in Charting

Hello! I am building a two year running total line chart (grey is last year, and blue is for current year). Is there a way for the current year running total line (blue) to not show anything beyond January (current month)? If not, is there a way to make future months dotted line or a different color? Since this is a running total but only January data is available, having a long horizontal line is distracting.

image.png

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can do a running total with a beast mode with a conditional add instead of using the running total on the card.

    CASE WHEN `Date` < CURRENT_DATE() THEN
    
SUM(SUM(`Sales`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)
    
END

    This will exclude any future dates today and beyond so you only have a line for the months provided.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    You can do a running total with a beast mode with a conditional add instead of using the running total on the card.

    CASE WHEN `Date` < CURRENT_DATE() THEN
    
SUM(SUM(`Sales`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)
    
END

    This will exclude any future dates today and beyond so you only have a line for the months provided.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • learningdomo
    learningdomo Member
    edited February 8

    Hi @GrantSmith, thanks for your response. I have been trying to apply your method to my data, but I still can't make it work on a line graph. I also partitioned by year, but the graph looks weird with the months duplicated.

    image.png

    Is there a way to create two running totals for this year and last year, and put them in the same graph, using your method? All I want is for the current year line is to not show future months, while last year line has all the month. Is that feasible?

  • GrantSmith
    GrantSmith Coach

    You can surround your beast mode in a case statement and check if the date is before today then return the date. This will return null for future dates

    Put the year in the series on your graph so it’ll be separate lines for each year.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories