"Less than equal to" in Set Analysis

[#1]

Hello,

I have table such as

ID      A          B          C

1        4          3          12

1        6          2          12

1       10          5          12

1       14          4          12

1       16          3          12

I want to create a chart table that gives average of B where A is less than/equal to C for ID. So I should have a table such as

ID      Value

1       AVG(3,2,5)

I am using expression

Avg({<A = {'<=$(C)'}>} B)

but somehow I am getting average of all the values of B and not where A is less than equal to C. Please note that this is not my actual table or data but a simple representation and all my values A, B and C are being calculated for each ID.

Can anyone help?

thanks

[prabhu0505]

Avg({<A = {"<=$(=C)"}>} B)

[maxgro · Accepted solution]

avg(if(A<C,B))

[#4]

Hi Sravana,

Thanks for the reply. Interestingly your solution works if there is only one ID but if I add more IDs then I get a blank table. Do we need to use aggregate or something?

thanks,

Manoj

[sunny_talwar]

You can also try this:

Avg({<UniqueIdentifierField = {"=A < C"}>} B)

[#6]

Hi Maxgro,

Again, works fine for one ID but if I have something like

ID      A          B          C

1        4          3          12

1        6          2          12

1       10          5          12

1       14          4          12

1       16          3          12

2        3          4          10

2        5          3          10

2        8          5          10

2       10          6         10

2       12          8         10

I get a blank table back.

regards,

[sunny_talwar]

Looking for this:

[#8]

Hi Sunny,

I don't have the licensed version so could you please copy paste the script? Also, it should be less than equal to.

thanks,

Manoj

[varunreddy]

Hi,

Try This:

aggr(Avg({<A = {"<=$(=C)"}>} B),ID)

Cheers!

[sunny_talwar]

Script:

Table:

LOAD *,

  RowNo() as UniqueIdentifier;

LOAD * Inline [

ID,      A,          B,          C

1,        4,          3,          12

1,        6,          2,          12

1,      10,          5,          12

1,      14,          4,          12

1,      16,          3,          12

2,        3,          4,          10

2,        5,          3,          10

2,        8,          5,          10

2,      10,          6,        10

2,      12,          8,        10

];

Dimension: ID

Expressions:

1) =Concat({<UniqueIdentifier = {"=A < C"}>} B, ', ')

2) =Avg({<UniqueIdentifier = {"=A < C"}>} B)