Skip to content

    • Charting

    Charting

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

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • 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.

      1. CASE WHEN `Date` < CURRENT_DATE() THEN
      2. SUM(SUM(`Sales`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)
      3. 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

    • 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.

      1. CASE WHEN `Date` < CURRENT_DATE() THEN
      2. SUM(SUM(`Sales`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)
      3. 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!**
    • Member
      edited February 2024

      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?

    • 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!**

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories