Filtered Measures Using Snapshots and Sisense Date Calculations

[npatel_02]

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: 

SUM(
CASE
WHEN [Days in dbt_valid_from] <= [@Stock_date]
AND (ISNULL([Days in dbt_valid_to]) OR [Days in dbt_valid_to] > [@Stock_date])
THEN 1
ELSE 0
END
)

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!

[akaplan · Accepted solution]

@npatel_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:

• Date Dimension: unique list of dates, for dashboard filtering purposes
• Inventory: collection of products in inventory, along with dbt_valid_from and dbt_valid_to (like your attached sample data)
• Inventory Status: Cartesian product of date & inventory UID.  This table contains Date, Inventory UID, and a calculated IsActive flag, which joins to the inventory table to check if the Date falls between dbt_valid_to and dbt_valid_from, producing a 1 or 0 for filtering purposes.

Inventory Status would then serve as the key between your date dimension and inventory tables.

Considerations:

• Size of Inventory Status table
• This is a supervised many-to-many, so you'd need to restrict the Date dashboard filter to single-select (not multi-select or range or anything like that).
• Ensure the widget(s) or dashboard are filtered to IsActive = 1 to return the desired inventory records

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.

[DRay]

Hello @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]

Hello @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.