6 Replies Latest reply: Oct 14, 2016 2:09 AM by Mervyn O'Connell RSS

    Aggr & set analysis

    Mervyn O'Connell

      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')

        • Re: Aggr & set analysis
          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

            • Re: Aggr & set analysis
              Mervyn O'Connell

              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')

            • Re: Aggr & set analysis
              x mg

              try

              =TIME(

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

                       aggr(

                           Max(PickTime)-Min(PickTime)

                       ,OrderNo

                       )

                  )

              ,'hh:mm'

              )

              • Re: Aggr & set analysis
                Martin Bacul�k

                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