ContributionsMost RecentNewest TopicsMost LikesSolutionsRe: Show only last few weeks of Running Total that's calculated from beginning of time 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. 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. SolvedRe: 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: Single Filter against multiple columns Hmm, I'm not sure of a way to do this out of the box. This might be something you could do with the BloX plugin, but I don't know enough about BloX to say for sure. If you have Blox, though, I'd explore that avenue first, if you can. If not, and if michael_qbeeq's suggestion isn't viable for you -- say, for example, you're like me and using an older version of Sisense that doesn't have that feature -- then you may just have to go with listing both fields in the dashboard filter and instructing users to only activate one or the other. Not ideal, I know. It might be possible to use a dashboard script to grab the value entered in one of the fields and then populate it into the other, then (with the same script or an additional one) use OR logic, rather than AND logic, for the two filters. That way, even though both filters will be listed, users only have to key in one of them. However, I don't know of a script off-hand that can do that. Maybe try searching the forums to see if you can find a somewhat-similar script (a dashboard script that's modifying the dashboard-level filters somehow) then seeing if you can use an AI chatbot to refine it down to something that'll work for your specific purposes. I've used this particular Sisense-specific chatbot in the past with some success, though like most LLM-based chatbots, it can sometimes get stuff pretty wrong. Here's an example of a dashboard script that might work as a starting point, I've used it to auto-set certain filter values at the dashboard level. Sorry I don't have more specific or helpful advice. Good luck! If you figure out a decent script to do this, let me know, as I'd also be interested in accomplishing something like this! Re: Widget Filters - condition across multiple fields Perhaps. I'll take a look and post a reply there 🙂 Re: Invert text color when over dark highlight I recently had to do this! After a lot of referencing the HighChart API and chatting with various AI's, I found a few things that worked, at least for us. Here's a script you could use for a Bar Chart: // This script allows you to change the formatting of the data labels in a chart widget.on('render', function(sender, se) { var series = sender.queryResult.series[0]; // if you have multiple series/values, change this number to specify which one to apply the below formatting to (starting from 0) series.dataLabels = series.dataLabels || {}; series.dataLabels.allowOverlap = false; // set to true to allow labels to overlap each other; will make more display but might be a mess // Text Highlighting series.dataLabels.style = { color: series.color, // the color of the data label text; can set manually with a hex value like '#FF0000', or use series.color to have it inherit the color you set in the left-hand pane for the values textOutline: '2px contrast' // adds an outline: first value sets the width, second sets the color; default is 'contrast' which automatically chooses for the most contrast; can set manually with a hex value or disabled by setting it to 'none' }; // Background Color series.dataLabels.backgroundColor = "rgba(255, 255, 255, 0.7)"; // sets background color using rgb values, plus the last number can be set from 0 (fully transparent) to 1 (fully opaque) series.dataLabels.borderWidth = 0; // width of the border around the background, set to 0 to turn off series.dataLabels.borderColor = '#000000'; // sets the color of the border around the background, use hex values like '#FF0000' series.dataLabels.borderRadius = 5; // controls how curved the borders are, set to 0 for square corners // Position & Alignment series.dataLabels.inside = true; // set to true if you want the labels inside the bar series.dataLabels.align = 'right'; // can be 'left', 'right', or 'center' series.dataLabels.y = 0; // optional: adjust the y position offset of the labels, in pixels; positive is up, negative is down series.dataLabels.x = 0; // optional: adjust the x position offset of the labels, in pixels; positive is right, negative is left }); This should also work on column charts, and I'm pretty sure with line charts as well. I've used it in combo charts (i.e. a line chart but with a second series formatted as a bar chart). Can't promise it'll work with other types of charts that have data labels, like area or pie charts; I haven't tested those. This is assuming you just have one item under the "values" section, in the widget set up. If you have multiple values then I think it'll only apply to the formatting to the first one. If you're trying to format multiple different value series, I think you could just copy/paste the script again and change the "series[0]" bit to "series[1]", "series[2]", and so on, though that's a bit clumsy. There's a more elegant way to do it within the same script and to have it defined by the series name instead of it's index position, but I'd have to dig back through my AI chat logs to find that 😛 Here's some examples with a bar chart: Before: After (using everything in that script I posted): Here's an example with just the outline (no background) and default alignment: I think there's more properties you could add to the script, if there's more text formatting you want to do. It should be possible to make the text bold or change its size. I'd recommend checking out the Highcharts API reference to see what other properties you can set (scroll the lefthand sidebar down to see the "dataLabels" section). I found the "try it!" demos to be really useful for testing out different options. Hope this helps! Re: Pivot 2.0 - Manipulating a Pivot Chart Would it be possible to add "after" images to this article? There's several screenshots showing the "original table" but not what the table will look like after manipulating the elements in question. 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).