5 Replies Latest reply on Nov 17, 2016 4:27 PM by saurabh.c.0

# Need help with Calculated field - Aggrea

Hello Guys,

I have a requirement. Need to create a calculated field out the data sample below. Below data shows per day basis say 5 jobs run and it captures start and end time. Now 11/03 is the current run date and all the jobs are not completed yet. I need to find out for each of the day ST_TIME for job A and END_TIME for JOB E (if job E is not complete, return NULL).

Input Data.

 DATE JOB NAME ST_TIME END TIME 11/1/2016 A T1 T51 11/1/2016 B T2 T52 11/1/2016 C T3 T53 11/1/2016 D T4 T54 11/1/2016 E T5 T55 11/2/2016 A T6 T61 11/2/2016 B T7 T62 11/2/2016 C T8 T63 11/2/2016 D T9 T64 11/2/2016 E T10 T65 11/3/2016 A T11 T71 11/3/2016 B T12 T72 11/3/2016 C T13 T73

Expected Output:

 DATE ST_TIME END TIME 11/1/2016 T1 T55 11/2/2016 T6 T65 11/3/2016 T11 NULL
• ###### 1. Re: Need help with Calculated field - Aggrea

Ayan,

Find my approach as reference below and stored in attached workbook version 9.3

Approach is based on LOD expression

ST_TIME HIGH : { FIXED [Date]:max([END_TIME])}

ST_TIME LOW:  { FIXED [Date]:min([ST_TIME])}

• ###### 2. Re: Need help with Calculated field - Aggrea

Hi Norbert,

Thanks a lot for your prompt response.

Need a small tweak. What if I want to add the Filter of Jobname on top of this? if (JOB_NAME) == 'E' then get the END TIme corresponding to job E. Otherwise return NULL.

Regards,

Ayan

• ###### 3. Re: Need help with Calculated field - Aggrea

Ayan

Something like this....

• ###### 4. Re: Need help with Calculated field - Aggrea

Hi Norbert,

My expectation is little Different. Please check the Data for 11/03. JOB D and E has not completed. I need to find out for each of the day END_TIME for JOB E (if job E is not complete, return NULL).

Input Data.

 DATE JOB NAME ST_TIME END TIME 11/1/2016 A T1 T51 11/1/2016 B T2 T52 11/1/2016 C T3 T53 11/1/2016 D T4 T54 11/1/2016 E T5 T55 11/2/2016 A T6 T61 11/2/2016 B T7 T62 11/2/2016 C T8 T63 11/2/2016 D T9 T64 11/2/2016 E T10 T65 11/3/2016 A T11 T71 11/3/2016 B T12 T72 11/3/2016 C T13 T73

Expected Output:

 DATE ST_TIME END TIME 11/1/2016 T1 T55 11/2/2016 T6 T65 11/3/2016 T11 NULL
• ###### 5. Re: Need help with Calculated field - Aggrea

You will need to open the attached twbx  in tableau 10.1.1