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

    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 ❤️ 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.