3 Replies Latest reply on May 1, 2018 1:31 PM by Okechukwu Ossai

# Create Filter to count distinct just in certain hours in certain weekdays

Hi Tableau community, I'm barely new at tableau and I'm trying to figure out how to make the following filter, I have 3 columns:

Id                      Created time               Rider id

98895349  29/04/2018 11:59:35 p.m.   5998622

98895230  29/04/2018 11:59:08 p.m.   6291980

98895142  29/04/2018 11:58:48 p.m.   4250687

I would like to make a filter that for example counts distinct the "Rider ID" like from Monday to Friday from 6 AM to 10 AM and on Friday also from 5 PM TO 11 AM and also all Saturday and Sunday.

Any help will be greatly appreciated, thanks in advance.

• ###### 1. Re: Create Filter to count distinct just in certain hours in certain weekdays

Hi Juan,

I'm not sure if I've read your criteria correctly, considering how time is measured. Trying to be more specific, do you mean these time periods each day as shown below?

If yes, then try the formula below.

Create calculated field [Rider Id Selection]

IF DATENAME('weekday',[Created time]) = 'Saturday' OR DATENAME('weekday',[Created time]) = 'Sunday' THEN

IF DATEPART('hour',[Created time]) >= 17 AND DATEPART('hour',[Created time]) < 11 THEN [Rider id] END

ELSE

IF DATEPART('hour',[Created time]) >= 6 AND DATEPART('hour',[Created time]) < 10 THEN [Rider id] END

END

This calculation will return all the Rider ids which met the specified conditions. You can do whatever you wish with this calculation. you can either count it or use it to form a filter.

Create a calculated field [# Rider Id Selection]

COUNTD([Rider Id Selection])

This gives a distinct count of all Rider ids which met the condition.

Create a calculated field [Rider Id Selection Filter]

[Rider id] = [Rider Id Selection]

To use this as a filter, put the field on the filter shelf and set to 'True'

Hope this helps.

Ossai

• ###### 2. Re: Create Filter to count distinct just in certain hours in certain weekdays

HI Okechukwu,

Im actually trying just to do a filter that does the following criteria:

,

For example if i have 2 columns, the first one created time, and the second one the number of sales, i want to sum all the sales in the intervals mentioned above.

• ###### 3. Re: Create Filter to count distinct just in certain hours in certain weekdays

Hi Juan,

You can use the revised calculated fields below;

Create calculated field [Rider Id Selection]

IF DATENAME('weekday',[Created time]) = 'Saturday' OR DATENAME('weekday',[Created time]) = 'Sunday' THEN

IF DATEPART('hour',[Created time]) >= 0 AND DATEPART('hour',[Created time]) < 24 THEN [Rider id] END

ELSEIF DATENAME('weekday',[Created time]) = 'Friday' THEN

IF (DATEPART('hour',[Created time]) >= 6 AND DATEPART('hour',[Created time]) < 10) OR

(DATEPART('hour',[Created time]) >= 17 AND DATEPART('hour',[Created time]) < 24)THEN [Rider id] END

ELSE

IF DATEPART('hour',[Created time]) >= 6 AND DATEPART('hour',[Created time]) < 10 THEN [Rider id] END

END

This calculation will return all the Rider ids which met the specified conditions.

Create a calculated field [Rider Id Selection Filter]

[Rider id] = [Rider Id Selection]

This is a Boolean expression. Put the field on the filter shelf and set to 'True'.

Hope this helps.

Ossai