Filtering Inventory Dashboard to a Specific Date Using dbt Snapshot (valid_from/valid_to)
Hey Laflet interesting use case, a classic inventory challenge,
I hope I can shed some light here, LMK if I can be clearer.
Per your questions;
1. I am sure there are a few ways to achieve this, the option below is definitely the more straightforward and quick way.
2. My approach involves counting the days between valid_from to valid_to, and then comparing this value in the dashboard date filter. To achieve this I would use a combo of a Day Diff custom column in the Elasticube (as you rightly assume)
daydiff([dbt_valid_to (Date/Timestamp, can be NULL for current records)],[dbt_valid_from (Date/Timestamp)])
This will actually give you the number of days the item was on inventory, starting from the valid_from date.
3. and then combine the custom column from step 2 above with the Filtered Measure plugin available on the Sisense Marketplace (free)
In this step, we calculate the Day Difference again, using the dashboard formula, but this time we compare the valid_from date in the pivot or formula, to the actual date filter on the dashboard filter pane. We will then compare this Day Diff to the day diff calculation from the custom column in the EC and filter out irrelevant records.
Please find another detailed article about this plugin here by , strongly recommended to review as it covers a few use cases which can help in this or at least inspire on using other approaches.
The formula should look like this, although it might need some modification (notice the '@' sign in the formula, in the second date, this is to indicate to the Filtered Measure plugin to reference that date filter:
min(DDiff([Days in dbt_valid_from (Date/Timestamp)],[@Days in dbt_valid_from (Date/Timestamp)]))
This can be achieved by creating a flag in a new formula which defines that if the Day Diff from the EC is larger or smaller (depending on your logic) then create a flag 1, then filter out those 1 values. This is also the place to include the logic for the NULL issue you mentioned above.
I hope this helps, it gets a bit complicated when trying to convey complicated things over a community comment hehe 🙂
Another way to achieve this would be by use of our powerful Advanced Dashboard Filters or Input Parameters plugin, though if it is for this specific dashboard only it may be a bit of an overkill, LMK if you'd like to try it out.
If you'd like to meet up and review this or anything else, please don't hesitate to reach out, we're always here to help,
We're always here to help 😊
Ido from QBeeQ
Feel free to subscribe to our newsletter to stay up to date with the latest QBeeQ news!
