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.
Sort 'sdiff' results
I'm trying to figure out how to calculate the elapsed time between two data points in a record and then sort the results into different buckets. We use an embedded version of Sisense so I can't edit anything via the 'Elasticube'. I'm trying to take all the records created by a person and calculate the time from when the record was created to the time it was completed. Then display how many records were completed within different time frames. Either by using a column chart or pivot table. The problem is that the 'sdiff' function requires an aggregation. I don't want to find the median or average, I want to calculate the time for every record. I tried slapping an 'ALL' in front of the 'sdiff' function but that gave me an error. I also tried (COUNT(RECORDS),(SDIFF(ENDTIME, STARTTIME))) in the hopes that I could then filter the results but get an unexpected token error. I also tried CASE WHEN (SDIFF(ENDTIME,STARTTIME) < 7200 THEN 1 ELSE 0 END but ran into the same problem of 'sdiff' requiring an aggregator. What do?
