14 Replies Latest reply on Jan 19, 2020 11:41 PM by Richard Sanchez

# Help with counting between two dates

Hello Tableau gurus,

I need to count active patients between 2 dates for a period of three years. I only have a start and an end date. If I create a calculated field for every year it works with next three formulas. But then I need three different bar charts to get what I want.

calculated field 1:

calculated field 2:

calculated 3:

Result with three separate bar charts on a dashboard:

I now want to combine those three calculated fields into one so I can use them in a singe bar chart so I can work more flexible with it. I came up with this combined formula:

Now the result is:

Ofcourse I can filter out the Null values but if you look closer, only the first year is correct. The other two don't match so something in my combined formula is wrong. Can anyone see my mistake?

Kind regards,

Richard

• ###### 1. Re: Help with counting between two dates

Richard,

but first just wanted to note that a standard approach

to counting when given start and end dates is to use a date scaffold:

Though it may make your dataset larger, it does provide quite a bit

of capability when grouping by year or showing daily patient counts, etc.

If that is not feasible for your data, we can revisit.

• ###### 2. Re: Help with counting between two dates

good morning

this is a variation of the 2 date problem - it feels like there is a single calendar for the based on date but the data are actual aligned with 2 separate calendars - one start the other end

you want the data aligned against a single calendar and one way to do that is with a date scaffold

it not difficult - just a few steps - I keep a solution on my blog with a downloadable How to solution on my public site - see  See it your way: Scaffold

if looks like this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 3. Re: Help with counting between two dates

Hi RICHARD,

Looked into your formula. It's looks fine.

Can you share a workbook with twbx format with some sample data?

Without looking into workbook it's difficult to say.

Thanks,

Shruten

• ###### 4. Re: Help with counting between two dates

Hi Jim,

I think your solution will work for me but the dataset is getting very big. I've tested it with 4 rows and after joining it with a scaffold date calendar of 3 years (3x365 days) the test dataset now has 4384 rows so it's becoming more than 1000 times bigger. My production dataset has 9200 rows and after doing this trick it it now has about 10 milion rows (also 10 times bigger).

Last 3 questions:

1. In my own solution I use a count distinct on the customer id to count my customers per year/month. Does your query return only unique values? In your example you mention Account ID and those numbers look unique in your dataset but in my dataset patients come and go so in a period of 3 years I have to count distinct the customer id per year/month. I find LOD calcs hard to understand.

2. How can I change the MDY approach you use to a Year or Quarter or Month?

3. Is it possible to create a scaffolf date calender that only contains months or years instead of on a daily basis and join it? I've tried to create one with only 3 values: 2017, 2018, 2019. The formula seems to work but if I look closer it does not give the right values. For example, if a patients comes in on the 5th of January 2018 and the formula checks  if min([datumveld])>=min([Start datum]) then that patient does not come up because Tableau makes it 1-1-2018 instead of 2018 so my scaffold date is smaller than the formula wants. Change it to datepart was the next thing I thought of but then the syntax does not accept the formula. Any ideas?

Kind regards

Richard

• ###### 5. Re: Help with counting between two dates

Hi Richard - the solution I gave you is focused on getting the data structure in a form that has a continuous date calendar and places your data against the calendar - once in that form you can make any number of calculations against the data - by looking at each date individually -

if you want to look at the month/year level just set the scaffold dates to the month and year in a date form - I would use the first day of the month like 1/1/2020   and then in the formulas to count patients the date comparison would be     datetrunc('month',(date))   which returns the first day of the month of date

the min goes inside the parents    like year(min(scaffold date))

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 6. Re: Help with counting between two dates

Hi Jim,

Thx for your reply. So a scaffold calendar only works when you create it with all days of a year so there is no escape in blowing the data set up a thousand times (3 years).

When I change MDY to Year or Month the staffold calc that sums up the patients explodes. Year 2018 only has to show 3 dummy patients and year 2019 has to show 4 patients.

I've attached a small dummy workbook with fake data and hope you can show me the way because I still can't figure it out. Thanks in advance!

Kind regards, Richard

Dummy dataset in the workbook:

Viz on Tableau public

• ###### 7. Re: Help with counting between two dates

Richard - did you forget to attach the workbook?

Jim

• ###### 8. Re: Help with counting between two dates

I published it to Tableau Public because I don't know how to attach it:

• ###### 9. Re: Help with counting between two dates

you can attach the workbook as follows

first click on reply in the lower right corner of the post

when the new window opens click here in the upper right corner

another window will open click in the lower right and

and you file manager will open so you can select the file to post

MAKE SURE IT IS A TWBX FILE WITH THE DATA EXTRACTED

jim

• ###### 10. Re: Help with counting between two dates

Hi Jim,

Thank you. I've attached it.

• ###### 11. Re: Help with counting between two dates

if you are content with month level totals ( I think that is what you said in the post

then the scaffold can be at that level

I replaced your scaffold like this

and the count is simply this

and it will return this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 12. Re: Help with counting between two dates

Hi Jim,

Awesome! Only one tiny thing left  ;-). In the dummy set id2 patient is active between 15-1-2018 - 15-4-2019 but in the line chart januari shows 0?

• ###### 13. Re: Help with counting between two dates

right you are

I had the end conditions wrong - this will correct that -

it puts ID 2 start back in January

JIm

• ###### 14. Re: Help with counting between two dates

Thank you Jim! Everything now works as needed!

Regards,

Richard