Show only last few weeks of Running Total that's calculated from beginning of time
Hello! I've got a Running Total formula that is working as expected, but it only works properly if my widget is showing back to the beginning of time. I'd like to filter it so I only see the running total over the last few weeks, but when I filter, it messes up the running count and it's no longer accurate. I need to somehow calculate the prior running total so the filtered range picks up from the right place and continues calculating correctly afterward. How can I do this?
NOTE: I do not have access to the Elasticube setup, so I cannot modify any tables, create any custom columns, run custom SQL, etc. I can only work with what I have available to me in the Sisense UI. Also, we're on an older Windows version of Sisense (v8.2.1.10110), not Linux, so I may not have all of the current features in Sisense :(
Anyway, the running total I'm calculating is the number of open matters at any given point in time. Each matter can have an "Open" event and a "Closed" event. This is on a custom table (we had someone create for us) called "Matter Events". Rather than each matter being a single row with open and close date columns, it's structured as just a "Matter Date" and "Matter Event" column, and matters can show on multiple rows, once for each time they were opened or closed.
From this, I calculate a running total by treating each Open event as a +1 while each Closed event is considered a -1. If you add up every event from the beginning of time up to a given date, it should equal what the open total was on that date. Generally speaking, if I measure from the beginning of time up through today's date, the total matches the actual current number of open matters. So far so good.
Here's the formula I'm using:
RSUM(
SUM([Matter Number],
IF(
([# of unique Matter Event Type],[Matter Event Type (filtered to "Open")])=1, 1, 0
)
)
-
SUM([Matter Number],
IF(
([# of unique Matter Event Type],[Matter Event Type (filtered to "Closed")])=1, 1, 0
)
)
)
Basically: the inner IF formulas should be evaluating to a "1" if they match the filter (Open/Closed). I calculate the Open and Closed counts separately and subtract one from the other to arrive at some Net total, at the level of each Matter Number. Then the Running Sum formula wraps this entire thing so it properly tracks over time.
This seems to work well in a line chart:
However: I just want to show the last 4 weeks of open counts. If I just filter the widget based on the Matter Event Date, I get this:
This is because the Running Total formula just starts from 0 each time. How can I avoid that? I know one way I can do that is to manually key in a static value into the formula to make it line up correctly. For instance, if I just add a "+160" to the end of the formula, everything lines up correctly:
This is essentially what I need. However, the way I got there isn't ideal because the dashboard in question will always be a rolling four week lookback, so I would have to manually update that adjustment in the formula each week. Also, it would be nice if I could just filter to any arbitrary date range and see accurate totals for that period without having to figure out the prior counts manually. Is there a way I can dynamically calculate this pre-filtered-date-range total so the Running Total works correctly?
I know there's an "ALL" formula that seems like it might be useful in this regard, but no matter where I try to place it in my existing formula, I can't get it to work without a syntax error of some kind.
Okay, figured it out on my own after messing with it enough. Essentially, you need to split the formula in two: a Running Total that calculates over the period in question, plus a secondary formula that is NOT a running total but explicitly calculates for the prior period.
In my case I set up a "Matter Event Date" filter at the widget level, filtered to the last 4 weeks (rolling). Then, I used this formula:
RSUM(
SUM([Matter Number],
IF(
([# of unique Matter Event Type],[Matter Event Type (filtered to "Open")])=1,1,0
)
)
-
SUM([Matter Number],
IF(
([# of unique Matter Event Type],[Matter Event Type (filtered to "Closed")])=1,1,0
)
)
)
+
(
(COUNT([Matter Number]),[Matter Event Type (filtered to "Open")],[Weeks in Matter Event Date (filtered to all weeks PRIOR to the last 4 weeks)])
-
(COUNT([Matter Number]),[Matter Event Type (filtered to "Closed")],[Weeks in Matter Event Date (filtered to all weeks PRIOR to the last 4 weeks)])
)Basically, for the second part, I switched from SUM to COUNT, which allows easier filtering. I added a Matter Event Date filter and used the "Advanced" tab to specify all weeks prior to the last 4. To do this:
- In the formula editor, click on the element in question ("Weeks in Matter Event Date") and select Filter
- In the filter pop-up, click the "Time Frame" tab
- In the dropdown at the top, select "Weeks" (or whatever time period you're using)
- Select "This & Last Week" (important, this sets up the advanced filter in the next step)
- Click the "Advanced" tab. There should already be some code in there showing a count of "2" and an offset of "0" (if you skipped step 4 then it won't say this).
- Change the count to "9999" and the offset to "4", then click OK. This will filter on all months (up to 9999) EXCEPT the last 4 (the offset).
This worked for me! The only minor irritation is that if you decide to filter for a different date range (e.g. the last SIX weeks, instead of 4) then you have to update both the widget filter and the filters inside the formula so they match, otherwise your totals will get all wonky again.
