Aggr & set analysis

[#1]

Hi all,

Many posts & questions exist around this topic but I cant see what I'm doing wrong.

Goal: Average time it took to "Pick" an order today.

Data (Orders being picked off a shelf)

OrderNo	PickTime
42	2016-09-11 15:27:52.000
42	2016-09-11 15:33:35.000
42	2016-10-11 15:36:48.000
79	2016-10-12 13:17:10.000
79	2016-10-12 13:19:37.000

This (step 1) works: (shows average time across all orders)

TIME( avg( aggr( MAX(PickTime)-MIN(PickTime) , OrderNo )) ,'hh:mm')

Step 2 involving SetAnalysis, I'm appearing to have an issue:

TIME(

avg( {<NUM(Floor(MAX(PickTime))) ={ '$(=NUM(Floor(Today())))' }>} aggr( avg( {<NUM(Floor(MAX(PickTime)))={' $(=NUM(Floor(Today())))' }>} MAX(PickTime)-MIN(PickTime) ) ,OrderNo) )

,'hh:mm')

I read elsewhere the correct syntax is Avg( {<Filter>} Aggr( Avg( {<Filter>} Number), [Search Id] ) ) , am I overlooking something?

Edit: Altered the set analysis after testing the date conditions

Conditional date testing:

IF(NUM(Floor(MAX(PickTime))) = NUM(Floor(Today())),'YES','NO')

[sunny_talwar]

Problem is that you cannot use functions on the left hand side of the set modifier (in red below)

TIME(

avg( {<NUM(Floor(MAX(PickTime))) ={ '$(=NUM(Floor(Today())))' }>} aggr( avg( {<NUM(Floor(MAX(PickTime)))={' $(=NUM(Floor(Today())))' }>} MAX(PickTime)-MIN(PickTime) ) ,OrderNo) )

,'hh:mm')

But before I can suggest anything, it would be better if you can provide the expected output based on your sample so that we can at least test it out a little

[#3]

Of course, I got caught up in the question and neglected the expected result.

OrderNo	PickTime
42	2016-10-11 15:27:52.000
42	2016-10-11 15:33:35.000
42	2016-10-11 15:36:48.000
79	2016-10-12 13:17:10.000
79	2016-10-12 13:19:37.000

Using the above data and assuming date(today()) = '2016-10-12', I would expect an outcome of '00:02' ie AVG('00:02')

Changing the data slightly (same day multiple orders): The *CALC column is for explanation only (does not actually exist)

OrderNo	PickTime	*CALC_MAX(PickTime)-MIN(PickTime)
80	2016-10-12 15:27:52.000	00:08
80	2016-10-12 15:33:35.000	00:08
80	2016-10-12 15:36:53.000	00:08
81	2016-10-12 13:17:10.000	00:02
81	2016-10-12 13:19:37.000	00:02

Again assuming date(today()) = '2016-10-12', I would expect an outcome of  '00:05' ie AVG('00:08,'00:02')

[Anonymous]

hope

TIME( avg(total aggr( MAX(PickTime)-MIN(PickTime) , OrderNo )) ,'hh:mm')

[#5 · Accepted solution]

try

=TIME(

    avg( {<PickTime={'=left(PickTime,10)=Date(Today())'}>}

         aggr(

             Max(PickTime)-Min(PickTime)

         ,OrderNo

         )

    )

,'hh:mm'

)

[mato32188]

Hi Mervyn,

I would rather create new table in script like:

YourOriginalTable:

LOAD OrderNo,

     PickTime

FROM xxx;

NewAverageTable:

LOAD max(PickTime)-min(PickTime) as Difference,

  OrderNo

Resident YourOriginalTable

Group by OrderNo;

After that create straight table in front-end:

Dimensions: OrderNo

                   PickTime

Expression: sum(Difference)/Count(DISTINCT OrderNo)

Maybe help.

BR

Martin

[#7]

Thank you very much for this Martin,

I may look at this approach in the future. Small tables may be more straight forward than complex expressions.

Kind regards,

Mervyn