
1. Re: Day of Week Filter
Shinichiro Murakami Apr 13, 2017 1:12 PM (in response to Jen Wilkinson)Hi Jen
Unfortunately, your data structure requires reshaping 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

Test 1 slot_SM_10.1.twbx 93.3 KB


2. Re: Day of Week Filter
Jen Wilkinson Apr 25, 2017 8:04 AM (in response to Shinichiro Murakami)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 Apr 25, 2017 2:28 PM (in response to Jen Wilkinson)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

Test 1 slot_SM_10.1_v2.twbx 92.4 KB


4. Re: Day of Week Filter
Jen Wilkinson May 16, 2017 5:26 AM (in response to Jen Wilkinson)Perfect! Thanks Shin

5. Re: Day of Week Filter
Jen Wilkinson May 16, 2017 5:26 AM (in response to Shinichiro Murakami)Thanks Shin

6. Re: Day of Week Filter
Shinichiro Murakami May 16, 2017 7:29 AM (in response to Jen Wilkinson)Hi Jen
Gland to help you.
And, could you mark my answer as correct to close the thread.
Thanks,
Shin