Get Data Based on Comparison of 2 Dates


I have a Dataset with 2 dates open_date,closed_date for a category(has 2 values open,closed).
I want to build a weekly bar chart with a count of category where the date range is selected for the column open_date but the date range should also be applied to closed_date as well.
For Ex:- 

Open DateClosed DateCategory







For the example above if i keep the bar chart as weekly and select the date between 2020-07-01 and 2020-07-07 it should give me a count 1 as the category open and closed between that date is 1.

Can somebody help with this



  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    Step 1.  make sure your measure is aggregatable .... so instead of  'Open' / 'Closed' use a 0 or a 1 so we can take the SUM() or AVG() .


    For your reporting requirement,  i think i would be VERY clear about the question I'm asking.  are you asking:

    • "at the start of the week, how many projects were open?" or are you asking
    • "how many projects were closed during the week?"


    They aren't the same thing, and by being clear in what you want, you can give your card a clear title and minimize confusion for the users.


    Either way, I would structure the data so that I have exactly one row per week and isOpen = 0 or 1.


    if you can have multiple categories, ex. cat 1, cat 2, cat 3, then you need one row per week per category and your metric.


    Jae Wilson
    Check out my Domo Training YouTube Channel

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.