Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
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 - Creator
Partner - Creator

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 - Creator
Partner - Creator

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

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!