Running Total Beast Mode with Fixed Functionality

lindsay_gillespie · 2025-02-06T12:58:56+00:00

There was an error rendering this rich post.

I’m working on a running total Beast Mode that tracks ticket sales by event. My current formula works fine:

SUM(SUM(tickets sold)) OVER (PARTITION BY EVENT ORDER BY DATE ASC)

However, these events reoccur yearly, and I want to compare ticket sales pacing to previous years while maintaining drill-down and filtering capabilities—hence, this must be done in Beast Mode, not ETL.

The goal is to track ticket sales pacing vs. the previous year on the same date (March 10, 2025 vs March 10 2024, vs March 10 2023). For example, in March, I want the view to default to March’s daily pacing without losing the running total before March. I've had success with the aforementioned beast mode but once I filter to the month I want to look at (I don't want to look at 365 days at a time) the running total restarts from the beginning of March.

Key requirements:

Must be filterable by multiple dimensions
Must be dynamic (ETL is not an option)

Any ideas on how to achieve this within Beast Mode?

Find more posts tagged with

Beast Mode

Accepted answers

All comments

brycec

I had a similar problem in which I wanted to track vacation and sick time accrual for our employees. I needed to show the balance as a running total without it losing any history and becoming inaccurate due to date range filtering. The solution that worked in this case was to change my leave transaction DataSet from row = individual transactions (draw-downs or accruals) to a DataSet that row = employee-bucket(sick or vacation)-month. In the DataFlow, I added the following Rank & Window tile:

This allowed me to just sum the balance by month on the card, and it wouldn't lose history when filtered because it was a running total in the DataFlow.

I recognize this might not solve your issue, but I am hoping it helps you brainstorm something that does.

The other key here is I added a column "Month" that is the same exact thing as "Date". But, what this allows you to do is use the two to get the most recent row by a desired grouping.

(CASE 

    WHEN `Month` = MAX(MAX(`Date`) FIXED (REMOVE `Month`)) THEN 'Yes'

    ELSE 'No'

END)

lindsay_gillespie

@brycec thanks so much for the response but unfortunately, is not applicable in this scenario as I must be able to select from any combination of about 10 filters on the dashboard and so none of this can be done on the ETL level.