Help creating a Conditional Average

[#1]

I'm trying to create an average of price where related underlying data falls within minimum and maximum parameters.

In my dataset I have Price and Price Score. I want to average Price when Price Score is between -3 and +3. I have created variables for min and max and named them "Minscore" and "Maxscore". I've also created an input box so I can change my score parameters on the fly.

Now I need to know how I write my expression to take my min and max values into consideration.

In concept it should be...where Price Score is >= MinScore and <= MaxScore, Average Price.

I've tried numerous iterations to address and none are working for me. It seems pretty straightforward and I feel like I'm missing something basic. Any help would be greatly appreciated!!

[renjithpl]

I am not sure about your question, is it something like this

=avg(if(Minscore>=PriceScore and Maxscore<=PriceScore, Price))

[Miguel_Angel_Baeyens · Accepted solution]

Or using set analysis:

Avg({< PriceScore = {'>=$(MinScore)<=$(MaxScore)'} >} Price)

Hope that helps

[#4]

Thanks for the quick reply Ranjit.

You hooked me up. I wasn't using "and" in my expression, just a comma.

Now I have this:

avg(if(PRICE_SCORE>=MinScore and PRICE_SCORE<=MaxScore,SALES_PRICE))

Works like a charm!

[#5]

Thanks Miguel. That worked too. I hate to sound like such a neophyte, but Set Analysis should be faster b/c it "simply" selects data b/t a set of parameters correct? Is there a good comprehensive resource w/in the QlikView community that more deeply explains the benefits and how-to's of Set Analysis?

[renjithpl]

Hi Miguel,

I have a short question, i am not good at Set analysis, is it really mandatory to learn set analysis to do better application. or if i am good at using if conditions properly, i can do as equal as a set analysis can do. ??

Ranjit

[Miguel_Angel_Baeyens]

Set Analysis is one consequence of one of QlikView's strengths, which is the use of in-memory information. Basically, the set analysis works as if you selected different values for different fields, and then you apply the chart's dimensions and expressions. Or the opposite: Your expressions will be affected by the selections done in the set analysis.

The most comprehensive resource is the Community itself. There are some webinars and papers in the Training menu, and several examples in the Share QlikViews section. I don't know of any manual, othern than the official Reference Manual that comes with the complete installation, though.

Regards.

[Miguel_Angel_Baeyens]

Hello Ranjit,

In itself it's not mandatory, since you can get the same results without it. But in terms of performance, better of using set analysis. Any conditional in your expressions can take several seconds to render the chart, while using set analysis may not take longer than one second. Of course, it depends on the complexity on the analysis and filters you want to apply, the memory and speed of your processor, some other hardware..., but I can tell you of lots of cases where the conditionals simply didn't worked because of cpu and memory usage, and they did smoothly with set analysis.

It's a question of how is QlikView built, rather than expertise using conditionals. Even so, there are cases where conditionals are the only way, but not because of the filter rather than how you want to display the information (e. g.: you want the subtotals show the average without decimals but each line show two decimals, silly example, but possible).

Hope this make some sense.

[renjithpl]

Hi Miguel,

Thanks for your valuable information regarding set alalysis, as you mentioned in the above thread, its one of important funcion of qlikview. I will try to learn how to use set alalysis in a better way. Hope our community will help me a lot in learning. I saw the presentation on set alalysis, but i was able to understand a little of it. I want to work on some real examples of set analysis.

Regards

Ranjit