topic Set Analysis - Conditional Average in QlikView

Set Analysis - Conditional Average

Anonymous — Tue, 03 Apr 2012 15:18:31 GMT

Hi,

I've been playing around with set analysis and know there is a way to do this but have so far been unable to figure it out. To simplify my problem, I have three fields, Account, Ratio, Stat. When the user selects an account, I would like to average all the Ratios for the other account numbers where Stat = the value for the selected account Stat.

I would imagine the formula would be something like the following but haven't had much luck so far

=avg({1<Stat={$(Stat)}>} Ratio)

Another important thing to note is that Ratio could be 'NA' instead of a number. If it is, I would need the formula to ignore that stat in the average.

Set Analysis - Conditional Average

Jason_Michaelides — Tue, 03 Apr 2012 17:17:42 GMT

I think you need something like:

=Avg({<Account=E(),Stat=P()>} Ratio)

Difficult to say without testing! Also, if the 'NA's cause a problem try RangeAvg() instead of Avg.

Hope this helps,

Jason

Set Analysis - Conditional Average

Anonymous — Tue, 03 Apr 2012 20:17:59 GMT

Thanks Jason! That helped! So the following formula works:

=Avg({1<Stat = P() > } Ratio)

Now what if I needed the count of all the accounts that have a ratio less than the ratio for the selected account?

I can use the following to count all the accounts with a value less than 0:

=Count({1<Ratio = {'<0'} Account)

But, I can't seem to replace the zero with selected account's ratio value. Any suggestions? This doesn't work:

=Count({1<Ratio = {'<Ratio} Account) or a myriad of other combinations I've tried.

Set Analysis - Conditional Average

Jason_Michaelides — Tue, 03 Apr 2012 21:37:06 GMT

Maybe something like Count({1<Ratio={"<Only({<Ratio=P()>} Ratio)"}>} Account)

I think your previous post is saying there will only be one account selected and therefore only one ratio value. If you can post your app I can have a bit of a play...

Set Analysis - Conditional Average

Anonymous — Wed, 04 Apr 2012 16:58:29 GMT

I can't get that formula to work and unfortunately I can't post a workbook here but I'll try to be more descriptive of my data. I think if I change the field "Stat" to "Group" it will make more sense.

The data is ordered this way

Account Group Ratio

1 A 0.5

2 B -0.3

3 A 0.1

4 A 1.5

5 C -1.2

6 C 0.25

7 B 2.0

So, if the user selects Account # 1. I would like to determine how many Accounts in the same Group (group A) have Ratios less Account # 1's ratio (0.5).

To make it simpler though, a formula that counts all the ratios less than the selected account's ratio will be a great starting point.

Set Analysis - Conditional Average

CELAMBARASAN — Wed, 04 Apr 2012 17:15:39 GMT

Hi,

May be try with this one

Count({<Account=,Group={'$(=MaxString(Group))'},Ratio={"<$(=Max(Ratio))"}>} Account)

Celambarasan

Set Analysis - Conditional Average

Anonymous — Wed, 04 Apr 2012 17:51:23 GMT

Thanks for everyone's help. Jason your second suggestion did work...I was just reconciling it to the wrong number so I thought it was incorrect.

So the final formula looks like this:

=Count( {1< Group=P(), Ratio = {"<$(Only({<Ratio=P()>} Ratio))"}>} Account)

This formula works also:

=Count( {1< Group=P(), Ratio = {"<$(Max(Ratio))"}>} Account)

Set Analysis - Conditional Average

Jason_Michaelides — Wed, 04 Apr 2012 18:35:49 GMT

Good stuff. Please mark the correct answer so others can see it!