Get Data Based on Comparison of 2 Dates

Reply
Highlighted
White Belt

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

Highlighted
Major Red Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.