topic Re: "Less than equal to" in Set Analysis in QlikView

"Less than equal to" in Set Analysis

Tue, 03 Nov 2015 18:16:25 GMT

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

Re: "Less than equal to" in Set Analysis

prabhu0505 — Tue, 03 Nov 2015 18:33:25 GMT

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

Re: "Less than equal to" in Set Analysis

maxgro — Tue, 03 Nov 2015 18:48:16 GMT

avg(if(A<C,B))

Re: "Less than equal to" in Set Analysis

Tue, 03 Nov 2015 18:56:39 GMT

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

Re: "Less than equal to" in Set Analysis

sunny_talwar — Tue, 03 Nov 2015 19:11:14 GMT

You can also try this:

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

Re: "Less than equal to" in Set Analysis

Tue, 03 Nov 2015 19:20:51 GMT

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,

Re: "Less than equal to" in Set Analysis

sunny_talwar — Tue, 03 Nov 2015 19:35:50 GMT

Looking for this:

Re: "Less than equal to" in Set Analysis

Tue, 03 Nov 2015 19:41:04 GMT

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

Re: "Less than equal to" in Set Analysis

varunreddy — Tue, 03 Nov 2015 19:42:01 GMT

Hi,

Try This:

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

Cheers!

Re: "Less than equal to" in Set Analysis

sunny_talwar — Tue, 03 Nov 2015 19:44:35 GMT

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)

Re: "Less than equal to" in Set Analysis

Tue, 03 Nov 2015 19:45:30 GMT

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

Re: "Less than equal to" in Set Analysis

varunreddy — Tue, 03 Nov 2015 19:48:15 GMT

What is the functionality of UniqueIdentifier?

Re: "Less than equal to" in Set Analysis

maxgro — Tue, 03 Nov 2015 19:48:37 GMT

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);

Re: "Less than equal to" in Set Analysis

sunny_talwar — Tue, 03 Nov 2015 19:50:52 GMT

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

Re: "Less than equal to" in Set Analysis

Tue, 03 Nov 2015 20:01:09 GMT

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

Re: "Less than equal to" in Set Analysis

Qrishna — Tue, 03 Nov 2015 20:01:40 GMT

A slight modification to @maxgro‌ suggestion:

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

Re: "Less than equal to" in Set Analysis

sunny_talwar — Tue, 03 Nov 2015 20:07:28 GMT

Why not just do this:

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

Re: "Less than equal to" in Set Analysis

Qrishna — Tue, 03 Nov 2015 20:10:42 GMT

We can go with that too.

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

Re: "Less than equal to" in Set Analysis

sunny_talwar — Tue, 03 Nov 2015 20:12:54 GMT

Make sense