set analysis complex

[#1]

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

[ChristofSchwarz]

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