"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

[#2]

Thanks Varun, I tried this earlier but still doesn't work for multiple IDs.

[varunreddy]

What is the functionality of UniqueIdentifier?

[maxgro]

with your data (table on the left)

I get the straight table chart on the right (expression is avg(if(A<C,B))), dimension is ID

script is

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

] (delimiter is spaces);

[sunny_talwar]

UniqueIdentifier field makes each row unique and makes it possible to include all those rows where A < C into the calculation. We don't have to create this field, if there is already a field available in the application which makes the combination of A and C unique

[#6]

Strange, same code works now. I must be doing something wrong earlier I guess. Thanks!

[Qrishna]

A slight modification to @maxgro‌ suggestion:

=avg(if(A<C or A=C ,B))

[sunny_talwar]

Why not just do this:

=Avg(If(A<=C, B))

[Qrishna]

We can go with that too.

i thought  =avg(if(A<C or A=C ,B))  expression may  give more clearer picture in understanding.

[sunny_talwar]

Make sense