2 Replies Latest reply: Nov 20, 2012 6:03 AM by hankargel RSS

    How to calculate avg of multiple data sets combined using set analysis?

      Hi there,

       

      I'm trying to get the average of two sets of data from different fields, using set analysis.

      Hopefully someone can help me out:

       

      Consider the following dataset:

       

      Date
      ActualTarget
      Jan1
      Feb2
      Mar3
      Apr4
      May

      Jun6
      Jul
      5

       

      I'd like to get the avg of the Actual values (past) and Target values (future) combined, based on the selected month and only if the fields contain a value.

      Thus avg when June is selected: (1+2+3+4+5)/5 = 3

       

      Is this possible using set analysis?

       

      I figured something like: avg({<Date={'<=$(vMaxDate)'},Month=, Actual={*}>} Actual + {<Date={'>$(vMaxDate)'},Month=, Target={*}>} Target)

      But this doesn't do the trick.

      Btw, I guarantee that the variable maxdate works (I've used it in other expressions).

       

      Does anyone have an idea?

       

      Thanks!