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

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!

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

Hi hankargel :

Try use this expresion instead of avg :

(sum({<Date={'<=\$(vMaxDate)'} >}  Actual) + sum({<Date={'>\$(vMaxDate)'} >}  Target))

/

(Count({<Date={'<=\$(vMaxDate)'} >}  Actual) + Count({<Date={'>\$(vMaxDate)'} >}  Target))

Best Regards

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

Thanks!

I also added a statement to count only those fields that contain a value:

(Count({<Date={'<=\$(vMaxDate)'},Month=, Actual-={""}>}  Actual

Works as a charm!