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

    Juan Garza

      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
          Okechukwu Ossai

          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
            Juan Garza

            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
              Okechukwu Ossai

              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