7 Replies Latest reply on Jan 8, 2017 8:00 AM by Vandana Samtani

Need Help in Calculation

Vandana Samtani Dec 4, 2016 10:19 AM

Hi Guys,

I am new to this forum and would need your help in a calculation. I am trying to figure out a count of timings for a station number and branch name and also add the logic below -

The users want to see

Any branch/station that has less than 4 timing counts per print
Any branch/station that has less than 2 timing counts per print at 6:00 PM or later colored in bold red.
Any branch/station that has less than 2 timing counts per print between 12:00 PM and 5:59 PM colored in blue.
A user should be able to select any data to check above data. Let me know if i can use a parameter to show them a date calender.

Thanks for your help,

Vandana

Sample1.twbx 21.0 KB

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?
Like Show 0 Likes(0)

Actions
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!
Like Show 0 Likes(0)

Actions
3. Re: Need Help in Calculation

Shinichiro Murakami Dec 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
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
5. Re: Need Help in Calculation

Shinichiro Murakami Dec 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
Re: Used DATEPARSE, having issues with AM/PM

Count_Brach_Station_SM_9.3_V2.twbx 73.4 KB
Like Show 0 Likes(0)

Actions
6. Re: Need Help in Calculation

Shinichiro Murakami Dec 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

Count_Brach_Station_SM_9.3_V3.twbx 74.4 KB
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions