-
1. Re: Need Help in Calculation
Vincent Baumel Dec 5, 2016 1:57 PM (in response to Vandana Samtani)In your sample data set, the Timings field all start with "12/30/1899". Does this mean that the actual date could be different in this field, or is it that one field is the date and the other field is the time?
-
2. Re: Need Help in Calculation
Vandana Samtani Dec 5, 2016 3:36 PM (in response to Vincent Baumel)The timings field that you are seeing in the data set is there could be different timings for that particular day when a show presentation can start.
I am using dates so that the user can select any data to see how many presentations are going on for that day.
The data is available for 21 day back and 10 days forward.
Let me know if you have any more questions and i also appreciate looking into my request.
Thanks!
-
3. Re: Need Help in Calculation
Shinichiro MurakamiDec 5, 2016 6:10 PM (in response to Vandana Samtani)
Hi Vandana
I'm not sure I understand your request correctly, anyways.
// Put Filter with continuous <4
[Less than 4 timing counts per print]
{fixed [Branch Name],[Station Name],[Prints], [Date]:count([Timings])}
[less than 2 timing counts per print at 6:00 PM]
{fixed[Branch Name],[Date],[Prints],[Station Name]:count(if datepart('hour',[Timings])>= 18 then [Timings] end)}
[less than 2 timing counts per print between 12:00 PM and 5:59 PM]
{fixed[Branch Name],[Date],[Prints],[Station Name]:count(if datepart('hour',[Timings])>= 12 and datepart('hour',[Timings])< 18 then [Timings] end)}
[Color Red / Blue]
if [less than 2 timing counts per print at 6:00 PM]<2 then "Red"
elseif [less than 2 timing counts per print between 12:00 PM and 5:59 PM]<2 then "Blue"
end
Thanks,
Shin
-
Count_Brach_Station_SM_9.3.twbx 34.3 KB
-
-
4. Re: Need Help in Calculation
Vandana Samtani Dec 6, 2016 8:02 PM (in response to Shinichiro Murakami)Hi Shin,
Thanks for looking into my request and giving me the right approach. Basically I am trying to check how many shows are live for any day that the user selects by station and branch.
I can use your approach for my request.
Quick question when i use the formulas above for "
[less than 2 timing counts per print at 6:00 PM]
{fixed[Branch Name],[Date],[Prints],[Station Name]:count(if datepart('hour',[Timings])>= 18 then [Timings] end)}
I am getting an error below..... the timing field in my database is a string. Examples in the database -
6:30 AM
5:00 AM
6:00 PM
7:00PM
Also to clarify and i tthink ableau picked up from my sample worksheet. The timing column just has time data and date is a separate column. I think that's where the confusion was.
the timing column has the data below in this format
6:30 AM
5:00 AM
6:00 PM
7:00PM
Can you please let me know how to resolve this issue?
Thanks again for your help,
Vandana
-
5. Re: Need Help in Calculation
Shinichiro MurakamiDec 6, 2016 9:24 PM (in response to Vandana Samtani)
Try this formula to convert string of time to time format.
Then use that field instead of "Timing"
[Timings calculated]
dateparse("yyyy-M-d-H:mm","1899-1-1-"+[Hour String])
Thanks,
Shin
reference
-
6. Re: Need Help in Calculation
Shinichiro MurakamiDec 6, 2016 10:00 PM (in response to Shinichiro Murakami)
Sorry, made mistake.
[Timings calculated]
dateparse("yyyy-M-d-hh:mm","1899-1-1-"+[Hour String])
+(if contains([Hour String],"PM") then 12 else 0 end)/24
Thanks,
Shin
-
7. Re: Need Help in Calculation
Vandana Samtani Jan 8, 2017 8:00 AM (in response to Shinichiro Murakami)HI Shin,
Happy New Year!
I wanted to thank you for helping me resolve this issue.
I have one more request. Calculation using Parameters Can you please help me with this?
Thanks,
Vandana