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.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!