topic Re: Minimum Set Analysis in QlikView

Minimum Set Analysis

c_latham — Wed, 18 Apr 2018 20:12:43 GMT

Hi All,

I've got the following Expression to calculate 'TotalTimeToPick':

=interval(Time((Max({$<FROM_LOC_ID={'Repack'}>}[Transaction DateTime])) - (Min({$<Transaction={'Pick'},[Transaction To Location]={'CONTAINER'}>}[Transaction DateTime]))))

The Dimension is ORDER_DATE.

This works correctly, I have also used this and a Count of OrderID to get me AverageTimeToPick.

I am trying to work out MinimumPickTime and MaximumPickTime per ORDER_DATE but when I get an error expression no matter which way I use 'Min' and 'Max'.

If anyone can help that would be great!

Thanks

Re: Minimum Set Analysis

vishsaggi — Wed, 18 Apr 2018 20:39:54 GMT

Where are you using these Max and Min in your file I mean in the same chart as you used Interval() or a different chart? Can you share a sample to look into?

Re: Minimum Set Analysis

c_latham — Wed, 18 Apr 2018 20:44:55 GMT

In the same straight table for example.

ORDER_DATE	TotalTimeToPick	AverageTimeToPick	MinimumTimeToPick
01/01/2018	1378	40	-
02/01/2018	100	10	-
03/01/2018	293	28	-

I cant share data now! I will in the morning when I'm at the PC with the full file.

Re: Minimum Set Analysis

vishsaggi — Wed, 18 Apr 2018 20:51:24 GMT

Ok. So what are the values you are expecting to see in your MinTimetoPick field?

Re: Minimum Set Analysis

c_latham — Wed, 18 Apr 2018 20:56:07 GMT

So on anyone date I can have multiple orders (all with their own total pick time). I want Min to show the smallest picktime.

OrderDate	OrderNo	PickTime
01/01/2018	1234	37
01/01/2018	1235	23
01/01/2018	1236	65
01/01/2018	1237	31
01/01/2018	1238	22

So I would expect Min for 01/01/2018 to show 22 based on the data above.

Thanks for your reply!

Re: Minimum Set Analysis

vishsaggi — Wed, 18 Apr 2018 21:07:59 GMT

Try this?

= Min(TOTAL <OrderDate> PickTime)

= Max(TOTAL <OrderDate> PickTime)

Re: Minimum Set Analysis

c_latham — Wed, 18 Apr 2018 21:55:32 GMT

Unfortunately not! I dont know if its because my PickTime is made using this:

=interval(Time((Max({$<FROM_LOC_ID={'Repack'}>}[Transaction DateTime])) - (Min({$<Transaction={'Pick'},[Transaction To Location]={'CONTAINER'}>}[Transaction DateTime]))))

So I am using:

= Min(TOTAL <OrderDate> (interval(Time((Max({$<FROM_LOC_ID={'Repack'}>}[Transaction DateTime])) - (Min({$<Transaction={'Pick'},[Transaction To Location]={'CONTAINER'}>}[Transaction DateTime]))))))

Re: Minimum Set Analysis

sasiparupudi1 — Fri, 20 Apr 2018 09:38:52 GMT

May Be Try

interval(

Time(

Min

(

Aggr(Max({$<FROM_LOC_ID={'Repack'}>}[Transaction DateTime]),ORDERDATE)

)

(

Aggr(Min({$<Transaction={'Pick'},[Transaction To Location]={'CONTAINER'}>}[Transaction DateTime]),ORDERDATE)

)

Re: Minimum Set Analysis

sasiparupudi1 — Tue, 24 Apr 2018 17:18:21 GMT

Hi, Did you manage to make this work?