Conditional Average

Thu, 09 Jan 2014 15:35:56 GMT

Hello Every one,

Happy New Year!!

1. I have business like Rates, Credits, Commodities.

2. Always one client Selected

Get average of sales value for each business for all clients, I was able to get this.

Now the problem is I wont get average of all clients for each business meets the threshold. I tried formula

but did not work, still gives me total average only.

My chart has one dimension that is Business

Here is My formula to get average

=Sum({<Year={$(=MAX(Year))},

ID={"=((Sum({<Year={$(=MAX(Year))},ID=>}Sales)/1000 )>=$(=only(ThresholdValue)))"}>} Sales)

Count({<Year={$(=MAX(Year))},

ID={"=((Sum({<Year={$(=MAX(Year))},ID=>}Sales)/1000 )>=$(=only(ThresholdValue)))"}>} Distinct ID)

in above formula

1. ID={"=((Sum({<Year={$(=MAX(Year))},ID=>}Sales)/1000 )>=$(=only(ThresholdValue)))"} will give me all ID whose value greater then threshold, (indirect set)but it is not working.

2. ThresholdValue is the drop down select

I am not sure what am i missing here. Above expression still gives me all clients ave for each business rather then threshold limit

Any idea please?

Thank you in advance

Dev

Re: Conditional Average

Thu, 09 Jan 2014 16:27:27 GMT

The problem with this is it is not considering the Dimension in expression, I believe in Indirect set analysis. If I select My dimension then it gives correct result. Some how force the dimension into indirect set analysis.

Re: Conditional Average

Thu, 09 Jan 2014 17:29:49 GMT

Any help please?

topic Re: Conditional Average in QlikView

Conditional Average

Re: Conditional Average

Re: Conditional Average