3 Replies Latest reply: Feb 15, 2012 3:39 PM by David Nelson RSS

    Average Maximum in Expression Calculation

    David Nelson Feb 14, 2012 5:12 PM

      (I had posted a similar question concerning minimum dates, but had already marked it as correct and didn't know what the etiquette here entailed, so I just posted a modified version.)

      I have two date fields, "update_date" and "add_date". There are multiple update dates for each corresponding customer, but only one add date, as shown:

       

      add_date
      11/7/2011 9:56:21 PM

       

      update_date
      11/15/2011 6:12:16 PM
      11/15/2011 4:46:01 PM
      11/15/2011 3:19:17 PM
      11/15/2011 1:07:36 PM

       

      What I need to do in a chart (via an expression named "Approval") is calculate the average difference between the latest update date and the submit date. What I have now (thanks to a helpful soul) is:

       

          = avg(aggr(interval(update_date-add_date,'DD'),update_date))

       

      which gives me the average minimum date. How would I calculate the average of all maximum (latest, most recent) dates?

      • 512 Views
      • Tags:
        • Re: Average Maximum in Expression Calculation
          Stefan Wühl
          Stefan Wühl Feb 14, 2012 7:16 PM (in response to David Nelson)

          How are add_date and update_date related? Do your tables look like this (linked by customer):

           

          LOAD * INLINE [

          Customer, add_date

          A,    11/7/2011 9:56:21 PM

          B,    11/8/2011 9:56:21 PM

          ];

           

          LOAD * INLINE [

          Customer, update_date

          A,11/15/2011 6:12:16 PM

          A,11/15/2011 4:46:01 PM

          A,11/15/2011 3:19:17 PM

          A,11/15/2011 1:07:36 PM

          B,11/15/2011 6:12:16 PM

          B,11/15/2011 1:07:36 PM

          ];

           

          What is your dimension in the chart? I've tried with Customer, but the expression you posted above does not work. I also do not understand why it calculates the average min interval. Surely I don't really understand your setting.

           

          It tried with above tables and created a chart with dimension Customer and then used:

          = avg(aggr(interval(Min(update_date)-only(add_date),'DD'),Customer))

          resp.

          = avg(aggr(interval(Max(update_date)-only(add_date),'DD'),Customer))

           

          which seem to calculate what I would assume as average min and max intervals (average over Customer in the chart total).

           

          See also attached.

           

          Regads,

          Stefan

           

          edit: corrected aggr dimension list