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

[#1]

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	Actual	Target
Jan	1
Feb	2
Mar	3
Apr	4
May
Jun	6
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!

[forte · Accepted solution]

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

[#3]

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!