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!
