Filtering beast modes using Card Start and End Date parameters

PodiumMason
PodiumMason Contributor
in Magic ETL

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) 

 

Opportunity Name

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. 

**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'

Comments

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    If I understand your question, you're trying to create a parameterized queries. 

     

    that doesn't really exist in Domo, so you can take a MySQL dataflow esque approach.

     

    Basically create a webform with the following columns and JOIN it to every row of your transaction dataset.  (then change your parameter values using the webform OR inline editor app).

    Capture.PNG

     

    you need to add _JoinCOL = 1 to your transaction data and then you can do an INNER JOIN via Fusion. 

    B/c it's a fusion, the data will update instantaneously whenever a user change the filter values.

     

    Now just modify your card so ActivityDate <= CardEnd_Date AND ActivityDate >= CardStart_date.

     

    Maybe add a URL link somehow to the webform / inline app editor page to make it easy to change filter paramters.

     

     

     

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Jarvis
    Jarvis Domo Employee

    Hi @PodiumMason, the suggestions from @jaeW_at_Onyx are going to be your best bet, as Domo doesn't current have the capability to do what you're asking unfortunately.

Categories