Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Bikoman
Contributor II
Contributor II

Set Analysis on combo chart

Hey guys,
I got a combo chart with some calculations with rangeavg (which I assume the problem is there).

Capture.PNG

The Infestation Level formula looks like this:  rangeavg(above(sum (hasHigh)/count(hasHigh),0,15))*100

the thing is only the bar chart on the left show the right data once I drill down, lets say the third bar chart (id - 6086) the data changes. I think I need set analysis to set the formula to look for each id alone.
thanks in advance.

Labels (2)
1 Solution

Accepted Solutions
Rodj
Luminary Alumni
Luminary Alumni

Hi @Bikoman ,

It's a little difficult to tell just what you are trying to achieve but I'm assuming you have drilled through a dimension to a child dimension to get the image you've included here? It appears you are trying to calculate an average of averages, that's generally not going to end well as it isn't going to give you the same number as the true average calculated at the lowest level of cardinality. You have your first calculation of an average:

sum (hasHigh)/count(hasHigh)

Then you are doing a rangeavg to it. This is just averaging the averages, rather than the actual figures.

If that's what you are trying to achieve then all good. Set analysis is useful if you want to restrict your calculation to a set of values, but I'm not sure that is what you are trying to do.  It's possible that using aggregation (see the help for the "Aggr" function) might be what you are after as it will allow you to aggregate the averages at the correct level. Putting this kind of data into a table first can be a good way of getting your head around what is happening.

Cheers,

Rod

View solution in original post

2 Replies
Rodj
Luminary Alumni
Luminary Alumni

Hi @Bikoman ,

It's a little difficult to tell just what you are trying to achieve but I'm assuming you have drilled through a dimension to a child dimension to get the image you've included here? It appears you are trying to calculate an average of averages, that's generally not going to end well as it isn't going to give you the same number as the true average calculated at the lowest level of cardinality. You have your first calculation of an average:

sum (hasHigh)/count(hasHigh)

Then you are doing a rangeavg to it. This is just averaging the averages, rather than the actual figures.

If that's what you are trying to achieve then all good. Set analysis is useful if you want to restrict your calculation to a set of values, but I'm not sure that is what you are trying to do.  It's possible that using aggregation (see the help for the "Aggr" function) might be what you are after as it will allow you to aggregate the averages at the correct level. Putting this kind of data into a table first can be a good way of getting your head around what is happening.

Cheers,

Rod

Bikoman
Contributor II
Contributor II
Author

worked perfectly thanks!

I used this in the formula:
rangeavg(Aggr( above( sum (hasHigh) / count (hasHigh),0 ,15),something)) * 100