6 Replies Latest reply on May 16, 2017 7:29 AM by Shinichiro Murakami

    Day of Week Filter

    Jen Wilkinson

      I have some data where the day of week format is set as text as 1234567. For example, 1000000 is Monday. Some of the lines of data contain more than one day of week, e.g. 1000500

       

      I am trying to create a day of week filter that will show all the data for a particular day.

       

      My current formula is:

      IF(CONTAINS([DOW],STR(1))) THEN "Monday"

      ELSEIF (CONTAINS([DOW],STR(2))) THEN "Tuesday"

      ELSEIF (CONTAINS([DOW],STR(3))) THEN "Wednesday"

      ELSEIF (CONTAINS([DOW],STR(4))) THEN "Thursday"

      ELSEIF (CONTAINS([DOW],STR(5))) THEN "Friday"

      ELSEIF (CONTAINS([DOW],STR(6))) THEN "Saturday"

      ELSEIF (CONTAINS([DOW],STR(7))) THEN "Sunday" ELSE "" END

       

      But I am finding that it is excluding lines of data that have multiple days, so if the data is 1000500 it doesn't show when the day of week filter is set to Friday, only Monday.

       

      I'd appreciate some advice on how to solve this little problem.

       

      Thanks,

        • 1. Re: Day of Week Filter
          Shinichiro Murakami

          Hi Jen

           

          Unfortunately, your data structure requires re-shaping data.

          I mean theoretically 7 sets of same data to represent 1~7 of weekday respectively.

           

          I used Union in this case.

           

          [DOW2]

          RIGHT(STR(100000000+[DOW]),7)

           

          [Amount 2]

          if [Table Name]="Clipboard_20170413T110619#txt" and mid([DOW2],1,1)="1" then [Amount]

          elseif [Table Name]="Clipboard_20170413T110619.txt" and mid([DOW2],2,1)="2" then [Amount]

          elseif [Table Name]="Clipboard_20170413T110619.txt1" and mid([DOW2],3,1)="3" then [Amount]

          elseif [Table Name]="Clipboard_20170413T110619.txt2" and mid([DOW2],4,1)="4" then [Amount]

          elseif [Table Name]="Clipboard_20170413T110619.txt3" and mid([DOW2],5,1)="5" then [Amount]

          elseif [Table Name]="Clipboard_20170413T110619.txt4" and mid([DOW2],6,1)="6" then [Amount]

          elseif [Table Name]="Clipboard_20170413T110619.txt5" and mid([DOW2],7,1)="7" then [Amount]

          end

           

          [Show Weekday]

          case [Table Name]

          when  "Clipboard_20170413T110619#txt" then "Monday"

          when "Clipboard_20170413T110619.txt" then "Tuesday"

          when "Clipboard_20170413T110619.txt1" then "Wednesday"

          when "Clipboard_20170413T110619.txt2" then "Thursday"

          when "Clipboard_20170413T110619.txt3" then "Friday"

          when "Clipboard_20170413T110619.txt4" then "Saturday"

          when "Clipboard_20170413T110619.txt5" then "Sunday"

          end

           

           

          Thanks,

          Shin

          • 2. Re: Day of Week Filter
            Jen Wilkinson

            Hi,

             

            Thanks for replying.

             

            I thought it might involve duplicating data.

             

            A couple of questions:

            Why does Sunday come first in the 'Show Weekday' field?

            How can I link the 'Show Weekday' field to the 'Day of Week' parameter?

             

            Thanks,

            Jen

            • 3. Re: Day of Week Filter
              Shinichiro Murakami

              Hi Jen

               

              It includes multiple data for the week which has more than one "non Zero" digits.

              But the formula is excluding all the other value than specific weekday under table name calc,

              I don't think there are duplicated data. (It's fall into null)

               

              [Amount 2]

              if [Table Name]="Clipboard_20170413T110619#txt" and mid([DOW2],1,1)="1" then [Amount]

              elseif [Table Name]="Clipboard_20170413T110619.txt" and mid([DOW2],2,1)="2" then [Amount]

              elseif [Table Name]="Clipboard_20170413T110619.txt1" and mid([DOW2],3,1)="3" then [Amount]

              elseif [Table Name]="Clipboard_20170413T110619.txt2" and mid([DOW2],4,1)="4" then [Amount]

              elseif [Table Name]="Clipboard_20170413T110619.txt3" and mid([DOW2],5,1)="5" then [Amount]

              elseif [Table Name]="Clipboard_20170413T110619.txt4" and mid([DOW2],6,1)="6" then [Amount]

              elseif [Table Name]="Clipboard_20170413T110619.txt5" and mid([DOW2],7,1)="7" then [Amount]

              end

               

              You can change the sort order as you like.

               

               

              Parameter

               

              [Parameter Conversion]

              case [Day of Week]

              when "1" then "Monday"

              when "2" then "Tuesday"

              when "3" then "Wednesday"

              when "4" then "Thursday"

              when "5" then "Friday"

              when "6" then "Saturday"

              when "7" then "Sunday"

              end

               

               

              [Filter ]

              [Show Weekday] = [Parameter Conversion]

               

               

               

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Day of Week Filter
                Jen Wilkinson

                Perfect! Thanks Shin

                • 5. Re: Day of Week Filter
                  Jen Wilkinson

                  Thanks Shin

                  • 6. Re: Day of Week Filter
                    Shinichiro Murakami

                    Hi Jen

                     

                    Gland to help you.

                    And, could you mark my answer as correct to close the thread.

                     

                     

                    Thanks,

                    Shin