Set Analysis :(

[johnso2080]

Hi, can anyone help? It's probably really simple but I'm struggling...

I have 5 fields:

Order Date

Customer ID

Customer Join Date

Order ID

Value

I need the set analysis expression that gives me the total value of orders where the order date is greater than the customers join date. The join dates/customers/orders involved will be defined by the users selections, but the total value should involve ALL orders (provided the order dates are >= selected customers join date).

Thanks

Darren

[Miguel_Angel_Baeyens]

Hi,

If all those fields are in the same table, the expression should look like this:

Sum({< [Order ID] = P({< [Order Date] = {">=[Customer Join Date]"} >}) >} Value)

Hope that helps.

Miguel

[johnso2080]

Thanks Miguel,

They are, unfortunately, in different tables :-s

Thanks

Darren

[Miguel_Angel_Baeyens]

Hi Darren,

First, try the conditional, it will take long if there are a lot of records, but this is the initial step:

Sum(If([Order Date] >= [Customer Join Date], Value))

Hope that helps.

Miguel

[johnso2080]

Hi Miguel,

This gives me the total value of orders within the selection where order date >= join date.

What I need is the total value of all subsequent orders in the application made by any of the customers that had an order within the users selection.

So as an example. I have 3 orders:

Order     OrderDate     CustomerID     JoinDate          Value

1            1 Jan 2012     1                    1 Jan 2012        100

2            2 Jan 2012     2                    8 Oct 2011         168

3            3 Jan 2012     1                    1 Jan 2012         412

4            2 Jan 2012     3                    31 Dec 2011      121

If the user selects 1 Jan 2012 as the order date, there is one customer (CustomerID 1) that had an OrderDate and JoinDate on the same day. I want to calculate the total amount that CustomerID 1 spent in all of their subsequent orders. (In this case the result should be 512).

If the user selected 2 Jan 2012 as the order date, there are no customers that had the same join date so I would want this to result in 0 (customers 2 and 3 joined previously)

Hope that makes sense? Appreciate your help!

Many Thanks

Darren

[Miguel_Angel_Baeyens · Accepted solution]

Hi Darren,

Yes, that does make sense. Here I see something more complex than just set analysis, perhaps the following:

Sum(Aggr(Sum({1} If(Aggr(NODISTINCT Only(OrderDate), CustomerID) <= JoinDate, Value, 0)), CustomerID))

From inside out: you take the OrderDate that corresponds to the one selected by the user, then compare it to the JoinDate. If this OrderDate is less than or equal to the JoinDate, then return Value, otherwise, return 0. But as long as you are going to select a value, and even so, you want to show values not directly related to the selection you have made, you need to Aggr() the value, them Sum() it for all records "{1}".

An easier way? Probably yes, doing some kind of flag field in the script, where you get if OrderDate is greater than JoinDate, for example.

Hope that helps.

Miguel