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

    set analysis complex

      Below an example of data :

       

      orderIDorderStatusorderDateorderAmount
      101/01/2014300
      13002/01/2014100
      14003/01/2014400
      15004/01/2014200
      12005/01/2014500
      16006/01/2014600
      23003/01/2014700
      24004/01/2014800
      205/01/2014900
      27006/01/20141000

       

      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
          Christof Schwarz

          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