Jake_Raz
ETL
Joined 11-14-2022
User Profile
User Widgets
Contributions
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.Re: How to make a histogram widget
Depending on what you're trying to count, you might not need to have anything in the Y-Axis/Category at all. What gabrielladawn was suggesting is manually creating multiple separate formulas under the Values section, one for each range/bucket you want to show as a bar. Here's something similar I did in my system, using a Column Chart instead of a Bar Chart: Here's what the widget setup looks like: Each one of those Values is a formula I created. Here's what the first one looks like (the rest are pretty similar, just swapping out numbers where appropriate): The only thing that stinks about this method is the manually creation of each bucket. If you want a LOT of buckets then that needs a lot of formulas, and the ordering of them is also entirely manual. I'm guessing that's what the Histogram plugin is supposed to do, it probably just automates this process so you can dynamically increase or decrease the number of bars shown.Filtering causes rows where another value is blank to be filtered out
Hello! I have a pivot showing a list of Matter Numbers, associated Firms, whether each firm has an MLSA date, and the total Invoice Fees for each firm on each matter. I want to filter down to only the rows where the total fees are greater than $250k, highlighted in the screenshot below as A, B, and C: Note how the "MLSA Date" is blank/null for A & B. Also, the fields used as rows in the pivot - Matter Number, Firm, and MLSA Date - are on different tables. Matters to Firm is a one-to-many relationship, but Firm to MLSA date should be a one-to-one relationship. Total Fees is on the same table as the Firms. Note: I do not have admin access to the backend, so I don't know for certain if this is how it's actually set up, I just know how they're supposed to function based on my knowledge of the data elements and the application they're coming from. If it helps, here's what our "Visualize JAQL" plugin shows: When I try to filter the "Invoice Net Fee Total" value for only rows greater than $250k, only C shows in the result, and A & B are filtered out, despite having over $250k in fees. I tried a different approach by using a formula that sums the Invoice Net Fee Total and resolves to a "1" if it's over $250k, and null if not. Here's what that looks like when no filter is applied (note how the highlighted values also have a "1" next to them, as expected): However, when I try to filter on this formula, the same thing happens - rows A & B are filtered out, despite having a "1" in the formula column (likely because they don't have MLSA dates): Why is Sisense doing this? I'm not filtering on the MLSA date, but it seems that filtering on the Total Fees will ALSO filter out the MLSA blanks for some reason. Our "Visualize Queries" plug-in seems to confirm that the widget is also filtering on the MLSA date for some reason (showing that field as "FD" meaning a dimension that's being filtered), despite me not actually filtering on it in the front-end: If I remove the MLSA Date from the widget, then everything works as expected; both of the above filtering methods will show the correct rows (A, B, and C). It's only when MLSA Date is present that the Total Fees filter will also filter out missing MLSA dates. I'm guessing it has to do with the fact that MLSA Date is on a different table, so there's some weirdness happening with the joins behind the scenes? When the filter is active, it's behaving like an INNER join, since the null values are being filtered out. However, why would it act like a LEFT/OUTER join when there's no filter applied? In any case, is there a way I can achieve the filtering I want AND also display the MLSA Date column without filtering out the blank/null MLSA dates? Please note: we're on an older Windows-based version of Sisense, v8.2.1.10110. Also, as a reminder: I don't have admin access to the backend, so I'm not able to modify anything with the eCube itself. I'd prefer to solve this with a formula if possible, but we can utilize a widget script if it's necessary. Thanks!Re: Widget Filters - condition across multiple fields
AlexW Hmm. Without seeing your underlying data, I couldn't say, sorry. I will say that, whenever I need to create formulas like this, I often run into issues with getting it to work in the way I'm expecting. I usually just keep experimenting (and searching the Sisense forums for useful info) until I find something that works. Perhaps there's an issue with one or more of the conditions? One thing you could try is to filter down to a particular record in your data that you know should be in the final output. Like, if you know Matter123 should show in the view because it meets all the conditions, then add a widget filter for the matter name and specify that one matter by itself. After that, try creating each condition as its own separate formula. Like, instead of chaining all them together with ORs/ANDs in the same formula, you would have each condition in its own formula that resolves to a 1 or 0. Then you can add them all to the view and validate, separately for each condition, whether it's correctly resolving to a 1. In order for your chained OR formula to work, at least one of the conditions must be resolving to a 1 (or if you're using AND, then all of them must). Another common issue to run into with this sort of formula are error messages in the formula editor. You didn't mention this but it might be something you encounter. Often this is because the underlying data query is pulling in multiple rows of data per item in the view, so you have to aggregate it somehow by wrapping certain elements of the formula in MIN, MAX, SUM, etc. Good luck! It can definitely be fiddly, sometimes. If you can't find a way to achieve this through formulas then you may need to look into the other solutions suggested.Re: Widget Filters - condition across multiple fields
Maybe try using Measured Values? You could create a formula, using an IF statement and chaining together your conditions with AND. If all conditions are true, output 1, else 0. Then you filter on that formula for only the items/rows with a "1". The tricky part is that, within the formula, when you're setting the conditions, you can't just say something like: [Field_1] = "Yes" This is because the IF formula only really works when you're comparing numerical values. However, you can construct the formula such that it still results in the same outcome. To do so, when you're in the formula editor, add two parentheses - "(" and ")" - and then inside the parentheses do the following: Use the data browser to search for the field you need Hover over it, select "More...", then "Count Unique" Type a comma (,) then re-add the same field, but this time just click on it directly After the closing parentheses, add " = 1 " It should look something like this: ( [# unique Field_1], [Field_1] ) = 1 Then you would click on the second item and select "Filter", and select the Yes value (or whatever value you're looking for. This will ensure that you're filtering down to only the records/items in the widget that contain a Yes value in that field. You can use this in an IF formula. If the condition evaluates to true, output a 1, else output a 0 (or NULL). Then you can just chain together multiple statements with AND's, so it only outputs the 1 if all conditions are true. The final formula would look something like this: IF( ( [# unique Field_1], [Field_1] ) = 1 AND ( [# unique Field_2], [Field_2] ) = 1 AND ( [# unique Field_3], [Field_3] ) = 1 AND ( [# unique Field_4], [Field_4] ) = 1, ,1,0 ) (Remember, you also need to click on each of those Fields and select "Filter" so it knows what it's looking for.) Then you filter your widget for when that formula is equal to 1. The nice thing is that, after you have the filter in place, you can hide the original formula so it doesn't display in the actual widget (since it was only necessary for the filtering).
