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

# Day of Week Filter

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

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

Hi,

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

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

Perfect! Thanks Shin

• ###### 5. Re: Day of Week Filter

Thanks Shin

• ###### 6. Re: Day of Week Filter

Hi Jen