Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Aggr & set analysis

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)

OrderNoPickTime

42

2016-09-11 15:27:52.000
422016-09-11 15:33:35.000
422016-10-11 15:36:48.000
792016-10-12 13:17:10.000
792016-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')

1 Solution

Accepted Solutions
Not applicable

Re: Aggr & set analysis

try

=TIME(

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

         aggr(

             Max(PickTime)-Min(PickTime)

         ,OrderNo

         )

    )

,'hh:mm'

)

6 Replies

Re: Aggr & set analysis

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

Not applicable

Re: Aggr & set analysis

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

OrderNoPickTime

42

2016-10-11 15:27:52.000
422016-10-11 15:33:35.000
422016-10-11 15:36:48.000
792016-10-12 13:17:10.000
792016-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)

OrderNoPickTime*CALC_MAX(PickTime)-MIN(PickTime)

80

2016-10-12 15:27:52.00000:08
802016-10-12 15:33:35.00000:08
802016-10-12 15:36:53.00000:08
812016-10-12 13:17:10.00000:02
812016-10-12 13:19:37.00000:02

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

maheshyadav
New Contributor III

Re: Aggr & set analysis

hope

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

Not applicable

Re: Aggr & set analysis

try

=TIME(

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

         aggr(

             Max(PickTime)-Min(PickTime)

         ,OrderNo

         )

    )

,'hh:mm'

)

mato32188
Contributor III

Re: Aggr & set analysis

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

Not applicable

Re: Aggr & set analysis

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