Get Data Based on Comparison of 2 Dates

user02319 · July 2020

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 · July 2020

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.

Get Data Based on Comparison of 2 Dates

Comments

Categories