1 Reply Latest reply: Feb 15, 2014 6:14 PM by Christof Schwarz

set analysis complex

Below an example of data :

 orderID orderStatus orderDate orderAmount 1 01/01/2014 300 1 30 02/01/2014 100 1 40 03/01/2014 400 1 50 04/01/2014 200 1 20 05/01/2014 500 1 60 06/01/2014 600 2 30 03/01/2014 700 2 40 04/01/2014 800 2 05/01/2014 900 2 70 06/01/2014 1000

I need 2 expressions :

Expression 1) count distinct OrderId where :

- OrderStatus at orderDate > OrderStatus at ComparaisonOrderDate

- orderStatus is not null at the two periods.

Expression 2) sum of orderAmount at orderDate - orderAmount at ComparaisonOrderDate

where

- OrderStatus at orderDate > OrderStatus at ComparaisonOrderDate

- orderStatus is not null at the two periods.

Examples 1:

when I select ComparaisonOrderDate=02/01/2014 and  orderDate=04/01/2014

Expression 1 should be: 1 (only orderID=1)

because 50 > 30 and 30 is not null and 50 is not null

Expression 2 should be: 100

200-100

Examples 2:

when I select

ComparaisonOrderDate=03/01/2014 and  orderDate=06/01/2014

Expression 1 should be: 2 (orderID=1 & 2)

because in orderID=1, 60 > 40 and in orderID=2, 70 > 30

Expression 2 should be: 500

(600-400) + (1000-700)

Regards,

Yacine

• Re: set analysis complex

Hi

Set-expressions cannot do the job here. You want to compare date-pairs and status-pairs per each orderID. A set would apply to the whole selection and would not be different in each row.

Here is my solution, if I got you right:

Load the order table again 1:1 but with a prefix comparision. Link the copy with the original via the common orderID

The two expressions are:

LET Expr1 = 'If(orderStatus > comparison.orderStatus AND Len(orderStatus) AND Len(comparison.orderStatus), Count(orderID))';

LET Expr2  = 'If(orderStatus > comparison.orderStatus AND Len(orderStatus) AND Len(comparison.orderStatus), Sum(orderAmount) - Sum(comparison.orderAmount))';

If you create a chart which has %orderID as a dimensionality, you can put those formulas as a expression. If you want to get the grand total of the two expressions outside such a chart dimensionality, create the sum or count based on an Aggr() pivot, creating the dimensionality ad-hoc.

Count(Aggr(\$(Expr1), %orderID))

or

Sum(Aggr(\$(Expr2), %orderID))

Hope this helps