Comparing a Running Sum to Same Time Last Year with nulls

ColinHaze
ColinHaze Member
in Beast Mode

Hi,

I am having a hard time comparing a running sum to the same time last year if there is a no value.

The beastmode I am using to get the running sum for each year is as followed.

SUM(Count(ReportNumber)) OVER (PARTITION BY Year ORDER BY AccidentDate)

However, not all accidents occur on the same day of the year, so my question is how can I modify the beast mode to find the max value leading up to the day of the year.

I attached a photo below to show you the issue. In the prior year coulmn I am trying to carry the two down until it becomes 3.

Is there an easier way to accomplish this? Thank you!

image.png

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    This doesn't answer your question, but a suggestion, since I built this card yesterday and have it on my mind, is to use the running total chart and add a YoY comparison and then show the data table under the running total line chart. This wouldn't require any beast modes and I think is a helpful visual.

    Screenshot 2025-01-06 at 8.58.45 AM.png

    If I solved your problem, please select "yes" above

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Interesting, it only seems to fill in when looking at a previous time period for the YoY comparison. When I use current year vs previous year it doesn't fill in.

    If I solved your problem, please select "yes" above

Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    This doesn't answer your question, but a suggestion, since I built this card yesterday and have it on my mind, is to use the running total chart and add a YoY comparison and then show the data table under the running total line chart. This wouldn't require any beast modes and I think is a helpful visual.

    Screenshot 2025-01-06 at 8.58.45 AM.png

    If I solved your problem, please select "yes" above

  • ColinHaze
    ColinHaze Member

    ColemenWilson

    I didnt know if DOMO had a card like that or not but its Exactly what I was looking for! You just saved me a lot of time thank you!

  • ColemenWilson
    ColemenWilson Coach

    Awesome! And you can of course switch it to graph by day, week, whatever to fit your needs. Glad I could help!

    If I solved your problem, please select "yes" above

  • ColinHaze
    ColinHaze Member

    ColemenWilson

    My only question is, how did you get the area to fill in gray?

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Interesting, it only seems to fill in when looking at a previous time period for the YoY comparison. When I use current year vs previous year it doesn't fill in.

    If I solved your problem, please select "yes" above

  • ColinHaze
    ColinHaze Member

    Odd, Thanks again!

Categories