Jake_Raz
ETL
Joined 3 years ago
User Profile
User Widgets
Contributions
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).Re: Creating a Year-Over-Year Chart in Sisense
alexism or any other users in the future: here's another solution that doesn't rely on any custom SQL or modifying the eCube, and instead just uses the PREV/NEXT formulas. I modified it slightly, for my purposes. If you use the current year as the baseline, you only see the months that've already occurred in the year (e.g. if it's February right now then your chart will only show January and February). Instead, I use last year as the baseline; this way we can see all 12 months in the chart. I'd set the date filter to Last Year, and then use the following formulas: CURRENT YEAR: ( SUM( [value] ) , NEXT( [date] , 12 ) ) LAST YEAR: SUM( [value] ) YEAR BEFORE LAST: ( SUM( [value] ) , PREV( [date] , 12 ) ) If you want to add more years, duplicate one of the NEXT/PREV ones and change the number to a different multiple of 12.
