Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
forte
Partner
Partner

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

View solution in original post

2 Replies
forte
Partner
Partner

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

View solution in original post

Not applicable
Author

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!