
Re: set analysis complex
Christof Schwarz Feb 15, 2014 6:14 PM (in response to Yacine Bousnina )Hi
Setexpressions cannot do the job here. You want to compare datepairs and statuspairs 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 adhoc.
Count(Aggr($(Expr1), %orderID))
or
Sum(Aggr($(Expr2), %orderID))
Hope this helps