Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
So, want I want to do is create a KPI that lets us know how many items were finished during our overtime periods (07:45 to 09:00).
I have job_instance_end_time_b which I have successfully used a table, but I am unsure how to write it up as a formula to filter between these times in a KPI.
Count({$<job_instance_end_time_b={'>7:45<9:00'}>}job_instance_end_time_b) is what I have so far, but this is not working as I had hoped (still learning this stuff!)
Thanks!
Ahhh OK, I get you. I have done as you said, but the values still are not changing
Can you add below line as 88th line. And take Filter for that new field and job_instance_end_time_b then filter on 1 then see whether it is matched or not??
LOAD
id_operator,
id_barcode,
id_job,
start_time as job_instance_start_time,
end_time as job_instance_end_time,
Year(end_time) as job_instance_end_time_year,
Month(end_time) as job_instance_end_time_month,
Day(end_time) as job_instance_end_time_day,
Date(end_time) as job_instance_end_time_date,
Week(end_time) as job_instance_end_time_week,
Time (end_time, 'hh:mm') as job_instance_end_time_b,
If(Time (end_time, 'hh:mm') > '07:45' and Time (end_time, 'hh:mm') <= '09:00', 1,0) as job_instance_end_time_b_Flag;
And, Use set analysis like below?
Count({<job_instance_end_time_b_Flag= {1}>}job_instance_end_time_b)
When I make a filter for the new field, the only option I get is 0. Set analysis does not give any results.
Please share file again
I can't work out how to upload files via this chat window, so another link https://pcko365-my.sharepoint.com/personal/robb_parris_pickeringrelay_com/_layouts/15/guestaccess.as...
Thank you for your help so far btw, really grateful.
Hi Robert,
In your load script try replacing
Time (end_time) as job_instance_end_time_b
with
Time (Frac(end_time)) as job_instance_end_time_b
As it stands the expression
Count(if(Time(Frac(job_instance_end_time))> '07:45' and Time(Frac(job_instance_end_time))< '09:00', job_instance_end_time_b))
returns a value of 13423.
Regards
Andrew
That seems to have worked! Thank you!
As far i see, there is not field called end_time. Can you show us where this field comes from
LOAD
id_operator,
// id_field,
// job_field_value,
id_barcode,
id_job,
// job_field_link,
start_time as job_instance_start_time,
end_time as job_instance_end_time,
Year(end_time) as job_instance_end_time_year,
Month(end_time) as job_instance_end_time_month,
Day(end_time) as job_instance_end_time_day,
Date(end_time) as job_instance_end_time_date,
Week(end_time) as job_instance_end_time_week,
Time (end_time, 'hh:mm') as job_instance_end_time_b,
If(Time (end_time, 'hh:mm') > '07:45' and Time (end_time, 'hh:mm') <= '09:00', 1,0) as job_instance_end_time_b_Flag;
SQL SELECT
s3_job_instance.id_operator,
s3_job_instance.id_barcode,
s3_job_instance.id_job,
s3_job_instance.start_time,
s3_job_instance.end_time,
FROM strip3.s3_job_instance;
Thank you for your help, but Andrews fix below has sorted my issue. Thanks again for all the time you put in helping!
Hi again!
How would I do the above if I wanted to filter it to a specific day? (saturday for example)
Based on how the rest is laid out, I'd assume I'd use:
Count(if(Time(Frac(job_instance_end_time))> '07:30' and Time(Frac(job_instance_end_time))< '17:00' and DayName(job_instance_end_time)='Sat', job_instance_end_time_b))
But that doesn't seem to work