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

Need Help in Calculation

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.

Vandana

• 1. Re: Need Help in Calculation

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

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

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

• 4. Re: Need Help in Calculation

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?

Vandana

• 5. Re: Need Help in Calculation

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

[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

HI Shin,

Happy New Year!

I wanted to thank you for helping me resolve this issue.