5 Replies Latest reply: Oct 4, 2012 6:14 AM by Miguel Angel Baeyens de Arce RSS

    Set Analysis :(

    Darren Johnson

      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

        • Re: Set Analysis :(
          Miguel Angel Baeyens de Arce

          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

            • Re: Set Analysis :(
              Darren Johnson

              Thanks Miguel,

               

              They are, unfortunately, in different tables :-s

               

              Thanks

               

              Darren

                • Re: Set Analysis :(
                  Miguel Angel Baeyens de Arce

                  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

                    • Re: Set Analysis :(
                      Darren Johnson

                      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

                        • Re: Set Analysis :(
                          Miguel Angel Baeyens de Arce

                          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