Filtering beast modes using Card Start and End Date parameters
Hey Dojo! Long time, no talk.
I'm back with an issue that's giving me one of those awesome tension headaches. Maybe y'all can help me out.
I'm building an opportunity progression dashboard for our sales team.
What the data looks like at the basal level is this: (Coming out of salesforce)
Record Insert date (One record per day per opportunity)
Closed Date (Always populated even if the opportunity isn't closed, sort of an "esimated closed")
Status (Closed Won, Closed Lost, etc.)
I'm looking to build a waterfall chart for a specific time period (Start and End Date). The issue I'm having is that some of the criteria for the waterfall "Buckets" are hard to manage with a date filter.
Ideally the user will specify a date "Between" two dates. Say January 1st - March 30th (Q1) , and the buckets would populate dollar values for the specified buckets.
The reality is that some of the bucketing relies on identfying opportunities that have a "Closed Date" that falls outside the specified date range.
For example, I want to see where an opportunity where the "Insert Date" of a record falls between the specified date range but also has an opportunity "Closed Date" in that date range also. The issue is that filtering the card on "Insert Date" could include records that have a "Closed Date" outside the date range.
This issue comes up when I try to do the opposite as well, wanting opporunities with "Closed Date" outside the specified "insert date" range.
Any ideas from the community at large? My initial thought would be to get all the dates into the same column some how, but it doesn't work because one opporunity can fall into multiple buckets potentially.
Is there a way to Reference the filter dates as variables in a beast mode? SSRS allows reference to parameters in report queries, I was wondering if I could do something like that in Domo at the card level. Like:
Case when `Insert Date` between @CardStartDate and @CardEndDate and `Close Date` between @CardStartDate and @CardEndDate then 'Closed In period' END
Let me know if you have any ideas.