npatel_02
Cloud Apps
Joined 5 months ago
User Profile
User Widgets
Contributions
Filtered measures across date ranges using a many-to-many relationship with dates
Hi there, Following on from the post regarding filtering inventory on a dashboard, I was able to create a pre-calculated date value for each row in the main inventory model, allowing for single-select filtering. Using the same date dimension and inventory model, I was hoping to employ filtered measures to create two columns each comparing the summation of values across two specific dates (like an opening and closing count of units). This is my current work around for getting the calculations across a date range, however I know that akaplan already considered that this would be difficult given the many-to-many relationship that exists between the models. Is this what is causing my issue here? As shared in the attached, it does not seem to apply measured filters at all now. Appreciate any advice/considerations on these filtered measures, Thanks in advanceSolved297Views0likes5CommentsFiltered 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: 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!Solved608Views0likes3Comments