Get Data Based on Comparison of 2 Dates

Hi,

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
2020-07-012020-07-07

Closed

2020-06-30 

Open

2020-07-022020-07-20

Closed

 

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

Tagged:

Comments

  • 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.

     

This discussion has been closed.