3 Replies Latest reply: Apr 23, 2010 8:50 PM by John Witherspoon RSS

    Show data in one chart based on different dimensions

      Hi All,

      A question about set analysis -- "show data in one chart based on different dimensions".

      Anyone can help? Thanks a lot!

      The basic tables look like following, and you can change.

       


      Customers:
      load
      CustomerID,
      Date(RegDate) as RegDate
      inline
      [
      CustomerID, RegDate
      1, 2009-01-01
      2, 2009-01-01
      3, 2009-01-02
      4, 2009-01-05
      ];

      Orders:
      load
      OrderID,
      CustomerID,
      Date(OrderDate) as OrderDate
      inline
      [
      OrderID, CustomerID, OrderDate
      1, 1, 2009-01-01
      2, 2, 2009-01-01
      3, 1, 2009-01-03
      4, 1, 2009-01-04
      5, 3, 2009-01-04
      ];


      What I want to see is # of Registers and # of Orders by day (register date and order date)

      Date, NbRegs, NbOrders
      2009-01-01, 2, 2
      2009-01-02, 1, 0
      2009-01-03, 0, 1
      2009-01-04, 0, 2
      2009-01-05, 1, 0

      Could anyone help to provide the expresssion?

      Thanks again!

        • SV:Show data in one chart based on different dimensions
          Jakob Berglund

          You don't need set analysis if you just create a new dimension named "Date" based on a concatenation of the two "RegDate" and "OrderDate". Then count the number of CustomerID and OrderID for each Date.

          A quicker way than count is to use sum, see attached example.

           


          Statistics:
          load
          RegDate as Date,
          CustomerID,
          1 as NbRegs
          resident Customers;

          concatenate
          load
          OrderDate as Date,
          OrderID,
          1 as NbOrders
          resident Orders;

          //the expressiones used to count NbRegs and NbOrders in the table later is sum(NbRegs) and sum(NbOrders) with the dimension Date
          //alternately, the slower way to calculate is to use count(distinct OrderID) and count(distinct CustomerID) with the dimension Date


          BR

          Jakob Berglund

          MILLNET BI