Set Analysis greater or equal to field

[didierodayo]

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

[sunny_talwar]

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

[didierodayo]

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

[sunny_talwar]

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

[vishsaggi]

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:

1

Just hide the expression in Presentation tab.

[sunny_talwar · Accepted solution]

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)

[didierodayo]

Thanks All the options above worked.