Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

h_demarco
New Contributor III

Dynamic SUMIFS in set analysis

Hi all,

I have a table that contains production and stops data of a machine:

Type_apDATE_TIME_STARTDATE_TIME_ENDTIME_ELAPSEDNET_TIME_ELAPSED
Production06/07/2016 05:2006/07/2016 10:575,63333335,633333334
Production06/07/2016 10:5806/07/2016 12:582,01666672,016666667
Production06/07/2016 12:5906/07/2016 13:390,68333330,683333333
Production06/07/2016 13:4006/07/2016 17:003,353,35
Production06/07/2016 17:0106/07/2016 21:594,98333332,983333334
Stop06/07/2016 17:0106/07/2016 17:480,8
Stop06/07/2016 18:0006/07/2016 18:200,35
Stop06/07/2016 20:0006/07/2016 20:500,85
Production06/07/2016 22:0007/07/2016 05:197,33333336,61666666
Stop07/07/2016 01:0007/07/2016 01:420,7166667

  

In Excel I am calculating using SUMIFS (example regarding first row)

=D2-SUMIFS($D$2:$D$11;$A$2:$A$11;"Stop";$B$2:$B$11;">="&B2;$C$2:$C$11;"<="&C2)

Which means that for each row I am subtracting STOP TIME_ELAPSED from PRODUCTION TIME_ELAPSED, but only if STOP START TIME is greater or equal than PRODUCTION START TIME and if STOP END TIME is lower or equal than PRODUCTION END TIME.

Stop time must be between production time to be subtracted and generated NET_TIME_ELAPSED.

Any suggestion on how to create NET_TIME_ELAPSED in Qlikview using Set Analysis?

Thanks

NET_TIME_ELAPSED was wrong. Instead of 4,9833333 should be 2,983333334 and instead of 7,3333333 should be 6,61666666

Tags (2)
11 Replies
MVP & Luminary
MVP & Luminary

Re: Dynamic SUMIFS in set analysis

Production 06/07/2016 17:01 06/07/2016 21:59 4,9833333 4,983333334
Stop 06/07/2016 17:01 06/07/2016 17:48 0,8

How can one machine do both Production and Stop during the same time?


talk is cheap, supply exceeds demand
galax_allu
Valued Contributor

Re: Dynamic SUMIFS in set analysis

Hi ,

try this , But you have look in to your Date formats ..

SUM( {<Type_ap={"stop"},DATE_TIME_START={">=$(=max(DATE_TIME_START))"},

            DATE_TIME_END={"<=$(=max(DATE_TIME_END))">}       TIME_ELAPSED)

h_demarco
New Contributor III

Re: Dynamic SUMIFS in set analysis

I agree with you.

But considering inputs are manuals, I will not change the logic of a whole factory if I can solve it in a simple way. Otherwise factory must hire more people just to be inputting every stop and restart.

MVP & Luminary
MVP & Luminary

Re: Dynamic SUMIFS in set analysis

That's fine, but I don't understand the logic. How is determined what should be subtracted from what? Unless you can give an exact algorithm for that I don't know how to create a solution.


talk is cheap, supply exceeds demand
h_demarco
New Contributor III

Re: Dynamic SUMIFS in set analysis

I need to subtract all stopped time, that are inside a production interval.

In example, my production shift is from 9am to 5pm. This interval contains some stops that I also know begin and end. First stop from 10am to 11am due to machine maintenance. Then I have another stop from noon to 1pm for operator lunch. So my net production elapsed time is not 8 hours, but 6.

h_demarco
New Contributor III

Re: Dynamic SUMIFS in set analysis

Did not worked. Field returned error.

My date and time formats:

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

galax_allu
Valued Contributor

Re: Dynamic SUMIFS in set analysis

try this

SUM( {<Type_ap={"stop"},DATE_TIME_START={">=$(=date(max(DATE_TIME_START),'DD/MM/YYYY')"},

            DATE_TIME_END={"<=$(=date(max(DATE_TIME_END),'DD/MM/YYYY')">}      TIME_ELAPSED)

MVP & Luminary
MVP & Luminary

Re: Dynamic SUMIFS in set analysis

Perhaps something like this:

Data:

LOAD

  DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED

FROM

  source_table

WHERE

  Type_ap = 'Production'

  ;

JOIN (DATA)

LOAD

  DATE_TIME_START as Stop_DATE_TIME_START,

  DATE_TIME_END as Stop_DATE_TIME_END,

  TIME_ELAPSED as Stop_TIME_ELAPSED

FROM

  source_table

WHERE

  Type_ap = 'Stop'

  ;

Temp1:

LOAD

  DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED - Stop_TIME_ELAPSED as NET_TIME_ELAPSED

RESIDENT

  Data

WHERE

    Stop_DATE_TIME_START >= DATE_TIME_START

  AND Stop_DATE_TIME_END <= DATE_TIME_END

  ;

Temp2:

LOAD

    DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED  

FROM

  source_table

WHERE

  Type_ap = 'Production'

  ;

JOIN (Temp2)

LOAD

    DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED    , 

  NET_TIME_ELAPSED

RESIDENT

    Temp1

  ;

Result:

LOAD

    DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED    , 

  sum(NET_TIME_ELAPSED) as NET_TIME_ELAPSED

RESIDENT

    Temp2

GROUP BY

  DATE_TIME_START ,

  DATE_TIME_END ,

  TIME_ELAPSED   

  ;

DROP TABLES Data, Temp1, Temp2;


talk is cheap, supply exceeds demand
h_demarco
New Contributor III

Re: Dynamic SUMIFS in set analysis

Not worked.

My guess is that error is related to DATE_TIME_* fields are timestamp, and the result should by NET_TIME_ELAPSED for each production interval, and not for a day.

When we use DATE_TIME_START={">=$(=max(DATE_TIME_START))", considering we are only summing STOP, how will QV understands that (stop) DATE_TIME_START should be >= (production) DATE_TIME_START if both fields have the same name? Probably I will have to rename fields on loading.

We are getting closer.