topic Re: Set Analysis greater or equal to field in QlikView

Set Analysis greater or equal to field

didierodayo — Mon, 06 Feb 2017 17:19:50 GMT

Hello,

I need some help working out this set analysis.

I have the table below and would like to return the following:

- For the max date

- where the QuantityA is greater or equal to QuantityB

Table:

Expected output:

Load Script:

SET:
LOAD * Inline
[Type,OrderDate,QuantityA,QuantityB
D, 29/01/2017, 12, 10
B, 29/01/2017, 4, 10
A, 15/01/2017, 4, 10
D, 29/01/2017, 7, 10
D, 10/01/2017, 4, 10
B, 25/12/2016, 4, 10
A, 18/12/2016, 4, 10
D, 29/01/2017, 10, 10
A, 20/11/2016, 4, 10
B, 23/10/2016, 4, 10
D, 16/10/2016, 4, 10
];

Regards

Re: Set Analysis greater or equal to field

sunny_talwar — Mon, 06 Feb 2017 17:23:59 GMT

This is need in the script? Just get the 2 rows from the 11 rows from your sample?

Re: Set Analysis greater or equal to field

didierodayo — Mon, 06 Feb 2017 17:28:13 GMT

No Sunny, I am try to represent this on a straight table. Yes I want the table to show the 2 type D where the quantity is greater or equal to QuantityB.

Thanks

Re: Set Analysis greater or equal to field

sunny_talwar — Mon, 06 Feb 2017 17:40:08 GMT

May be this?

=If(Avg({<OrderDate = {"$(=Date(Max(OrderDate), 'DD/MM/YYYY'))"}>} QuantityA) >= Avg({<OrderDate = {"$(=Date(Max(OrderDate), 'DD/MM/YYYY'))"}>} QuantityB), Avg({<OrderDate = {"$(=Date(Max(OrderDate), 'DD/MM/YYYY'))"}>} 1))

Re: Set Analysis greater or equal to field

vishsaggi — Mon, 06 Feb 2017 17:42:39 GMT

I am not sure if this is good way but you can try

Dim:

= IF(QuantityA >= QuantityB, Type) /* Check Supress When value is null for this Dimension */

OrderDate

QuantityA

QuantityB

Expr:

Just hide the expression in Presentation tab.

Re: Set Analysis greater or equal to field

sunny_talwar — Mon, 06 Feb 2017 17:46:11 GMT

Other option is to create a flag in the script

SET:

LOAD *,

If(QuantityA >= QuantityB, 1, 0) as Flag;

LOAD * Inline

[Type,OrderDate,QuantityA,QuantityB

D, 29/01/2017, 12, 10

B, 29/01/2017, 4, 10

A, 15/01/2017, 4, 10

D, 29/01/2017, 7, 10

D, 10/01/2017, 4, 10

B, 25/12/2016, 4, 10

A, 18/12/2016, 4, 10

D, 29/01/2017, 10, 10

A, 20/11/2016, 4, 10

B, 23/10/2016, 4, 10

D, 16/10/2016, 4, 10

];

and then this expression:

Only({<OrderDate = {"$(=Date(Max(OrderDate), 'DD/MM/YYYY'))"}>}Flag)

Re: Set Analysis greater or equal to field

didierodayo — Mon, 06 Feb 2017 18:04:13 GMT

Thanks All the options above worked.