Creating a KPI that shows how many items were finished within specific time ranges

[#1]

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!

[#2]

Ahhh OK, I get you. I have done as you said, but the values still are not changing

[Anil_Babu_Samineni]

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)

[#4]

When I make a filter for the new field, the only option I get is 0. Set analysis does not give any results.

[Anil_Babu_Samineni]

Please share file again

[#6]

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.

[effinty2112 · Accepted solution]

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

[#8]

That seems to have worked! Thank you!

[Anil_Babu_Samineni]

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;

[#10]

Thank you for your help, but Andrews fix below has sorted my issue. Thanks again for all the time you put in helping!

[#11]

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