Filtered Measures Using Snapshots and Sisense Date Calculations
EDIT: Sample data-set attached
Hi there,
I'm working on a dashboard that needs to accurately filter inventory records based on dbt snapshot dates (dbt_valid_from and dbt_valid_to) in SiSense. I face a similar challenge to another recent user post using filtered measures.
However, due to grouping/aggregation behavior in Sisense — where date fields must be wrapped inside aggregation functions like MIN() or MAX() — the original approach (direct comparisons inside calculated measures) did not work properly across different pivot groupings. When comparing the dbt date fields, Sisense forces you to aggregate (MIN or MAX) because of the pivot groupings.
However, I believe this distorted the date logic (allowing a date that could not qualify, slip through, as it calls for a min or max value).
I was able to show the specific counts across a range of dates however, using the filtering method explained here, of course, this only works on a range, and I require what the filtered date values would be on a specific date.
In an attempt to try another approach, I wrote the following as a calculated measure in the widget:
The idea here was to create a simple in-stock flag based on whether a record is "active" on the user-selected dashboard filter date (as per a filtered measure) recording a 1 (qualifies) or 0 (does not qualify).
However, when using this measure inside the widget, Sisense throws an error (unexpected token or calculation failure), and breaks the widget (unknown error + no data showing).
Additionally, I noticed in previous experiments that trying to calculate day differences (using day diff) between the valid to, valid from, and the dashboard filter, also did not behave correctly — most records had a day difference of 0, even when logically they should qualify (have a 1). I suspect this was due to the timestamps that are used for the date records not being correctly read.
Seeking guidance on:
- Is the general approach valid? Or am I missing something around the use of dates or aggregation inside Sisense calculated measures.
Is there a better way to model this comparison between dbt_valid_to, dbt_valid_from and the dashboard filter, given that Sisense enforces aggregation like MIN/MAX at the pivot level?
Any help or examples would be really appreciated!
Thanks!
DRay
·1 year agoHello @npatel_02,
I’m following up to see if the solution offered by @akaplan worked for you.
If so, please click the 'Accept as Solution' button on their post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
DRay
·1 year agoHello npatel_02,
I’m following up to see if the solution offered by akaplan worked for you.
If so, please click the 'Accept as Solution' button on their post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
akaplan
·1 year ago · Editednpatel_02 - how many inventory records are involved, and how many stock dates are relevant to the use case? The reason I ask is because a potential modeling solution involves pre-calculating the stock status for each product/date combination. It makes things quite straight-forward, but there are potential limitations depending on data volume / disk size.
To limit the size as much as possible, the example would employ 3 tables:
Inventory Status would then serve as the key between your date dimension and inventory tables.
Considerations:
If this approach isn't feasible, I do believe we could come up with something a bit more dynamic with a combination of scripting and/or BloX actions. But this may require a more formal engagement with our Field Engineering Services team.