Get Data Based on Comparison of 2 Dates

user02319

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 Date	Closed Date	Category
2020-07-01	2020-07-07	Closed
2020-06-30		Open
2020-07-02	2020-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

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.