7 Replies Latest reply on Mar 13, 2017 1:52 PM by Mithil Mangalore

# Applying Distinct Count of Days # to All Data

Hello! I am attempting to create a highlight table of average resource utilization by time of day and resource through tableau. Here is what i have so far:

This is based on a full year of data. I took the total # of days that each resource was in use by time of day and divided it by total days of operations.

As you can see above, there are some gaps in my data. This is because if a patient did not arrive to that resource within the 30 min interval in the data, my COUNTD formula does not compute for that resource & time frame combination.

I used the following formula: { FIXED:COUNTD(DATE([EventTime]))}

Is there another way to count distinct days and have it appear for all resources and time frames?

Thanks for your help!

Karina

• ###### 1. Re: Applying Distinct Count of Days # to All Data

Hi  Karina,

Try using this:

{ FIXED [Resource], [TimeFrame]:COUNTD(DATE([EventTime]))}

Thanks

Deepak

• ###### 2. Re: Applying Distinct Count of Days # to All Data

Thanks Deepak, but this isn't really want i'm looking for..

I need 253 - which is the total days of operation to populate all cells in this grid. This formula changes the count by resource & time frame and still leaves blanks in my data.

• ###### 3. Re: Applying Distinct Count of Days # to All Data

can you attach workbook and let me know what you need exactly?

• ###### 4. Re: Applying Distinct Count of Days # to All Data

Unfortunately the data contains patient info, so I can't share the file.

I have a total of # of patients using each resource in each 30 minute interval for 2016 based on a runnning_sum calculation looking at arrival and departure times:

So for all days that we were open in 2016 - bed #2 was used by 160 patients at 1:30pm.

I need to divide these numbers by total # days of operation (in this case, 253) to get the average utilization rate. However, because no one new actually arrived to Bed#2 between 1:30-2pm in my data - my countd formula is not showing the 253 total for that cell

There's just no time stamp for that resource & time frame - but we were still open during that time.

As a result my avg utilization calculation is blank during that time:

Sorry that this is so confusing. I was hoping to find an easy way to just count distinct days that won't change when I apply it to different dimensions.

• ###### 5. Re: Applying Distinct Count of Days # to All Data

I am sorry Karina, I can't go beyond this before looking at data as it would be all guess work.

Thanks

Deepak

• ###### 6. Re: Applying Distinct Count of Days # to All Data

Hi Karina,

What data source are you using here? Would it be possible to create one table with all possible dates and one for the possible times and do a left join? You may be able to handle the issue this way.

Let me know if it works.

Regards,

Mithil

• ###### 7. Re: Applying Distinct Count of Days # to All Data

Try this formula for Total Days of Operation:

{ EXCLUDE [Resource] : COUNTD(DATE([EventTime]))}