3

Below is my calculation field 'Time':

if DATEPART('hour',[CreateTime]) >=17 AND DATEPART('hour',[CreateTime]) <19 then "17~19peak"
elseif DATEPART('hour',[CreateTime]) >=15 AND DATEPART('hour',[CreateTime]) <17 then '15~17'
elseif DATEPART('hour',[CreateTime]) >=19 AND DATEPART('hour',[CreateTime]) <21 then '19~21'
else 'other hour'
end

enter image description here

I drag a field called 'workornot', so if our employees are working at that time, it will show 1.

What I want to do is I want to know if our employees work in peak hour, but I am not sure how to set the calculation field of this one. Thanks

If '17~19 peak'=1 and '15~17'=1 and '19~21'=1 Then 'full day'
elseif '17~19 peak'=1 and '15~17'=0 and '19~21'=0 Then 'peak hour only'
elseif '17~19 peak'=0 and '15~17'=1 and '19~21'=0 Then 'before peak hour'
elseif '17~19 peak'=0 and '15~17'=0 and '19~21'=1 Then 'after peak hour'
else 'not important'
end

Updated: (not what I expect) enter image description here

Lara19
  • 615
  • 1
  • 9
  • 20

2 Answers2

2

It is not easy to help you without some more information on the structure of the data source but I think that your problem is that you cannot use a simple IF/ELSE statement for the final part of your task, as you have to consider a specific level of detail (the single employer). Another problem is that on your data source, only the worked shifts exist so you have to manage the difference between a worker shift (=1) and a not worked shift (which can be 0 o NULL or even do not exist).

I would suggest first making the Time calculated field, exactly as you mentioned.

Then create each specific LOD for each shift where you specify that the level of detail is Employer.

Worked_1719

// Employer worked 17~19 peak
// Name of field: Worked_1719
IFNULL({ FIXED [Employer ID] : MAX(If [Time]='17~19 peak' THEN 1 ELSE 0)},0)

Worked_1921

// Employer worked 19~21 peak
// Name of field: Worked_1921
IFNULL({ FIXED [Employer ID] : MAX(If [Time]='19~21k' THEN 1 ELSE 0)},0)

Worked_1517

// Employer worked 15~17 peak
// Name of field: Worked_1517
IFNULL({ FIXED [Employer ID] : MAX(If [Time]='15~17k' THEN 1 ELSE 0)},0)

At this point, you would be able to make a table with Employer ID in the column and the newly calculated fields Worked_1517, Worked_1719, and Worked_1921 in the rows. Finally, you can add a modified version of your workornot field:

// Name of field: Workornot
If [Worked_1719]=1 and [Worked_1517]=1 and [Worked_1921]=1 Then 'full day'
elseif [Worked_1719]=1 and [Worked_1517]=0 and [Worked_1921]=0 Then 'peak hour only'
elseif [Worked_1719]=0 and [Worked_1517]=1 and [Worked_1921]=0 Then 'before peak hour'
elseif [Worked_1719]=0 and [Worked_1517]=0 and [Worked_1921]=1 Then 'after peak hour'
else 'not important
end
Nicolaesse
  • 2,554
  • 12
  • 46
  • 71
  • 2
    FYI, I find it clearer to write "INT(MAX([Time]='17~19 peak'))" instead of "MAX(If [Time]='17~19 peak' THEN 1 ELSE 0)". The two expressions are equivalent, the shorter one just takes advantage of how INT() and MAX() apply to boolean fields – Alex Blakemore Aug 28 '23 at 18:41
  • Hi! I just updated my tableau by using your method. It looks weird to me. Not sure where I do wrong. Thanks – Lara19 Aug 29 '23 at 03:47
  • MAX() will return the highest value that appears in a column. Tableau treats True values as greater than False values, so MAX() will be True if is satisfied for *any* record, while MIN() will be True if is satisfied for *every* record. Both aggregation functions ignore nulls. Finally, the INT() function converts True to 1 and False to 0. – Alex Blakemore Aug 29 '23 at 21:26
1

I believe you already have everything you need to show the data you wish to see.

  • You could simply add your [time] calculation to the filter card and set it to "17~19peak".
  • You could make a grid and add your [employee name/id] to the rows and keep [time] as the columns. (Alternatively since you have made timed "buckets" making this viz into a bar chart and adding employees to rows should show you exactly what you are looking for without the need for another calculation.)

As for your second calculation, you can compare your [time] and [workornot] calculated fields in a Nested IF Statement ; such as:

(Careful comparing data of differing types; maybe STR([workornot]))

IF [time] = '15~17' AND STR([workornot]) = '1' THEN  
   IF [time] = '17~19peak' AND STR([workornot]) = '1' THEN...

** Note: this only works if your [CreateTime] field is already at an employee level of detail

MUFF.
  • 178
  • 5