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

learningdomo

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.

GrantSmith

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.

GrantSmith

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.

learningdomo

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.

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

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.