5 Replies Latest reply: Nov 24, 2010 12:21 PM by Roland Kunle RSS

    Data Model and Set Analysis

      Hello

      I want help on how best to model the data and if possible the kind of set analysis to use

      I have two tableS: SALES_DATA and RECEIPT_DATA with thier fields and sample data described below:


      SALES_DATA
      ===================
      SALES_DATE,
      SALES_NUMBER
      SALES_AMOUNT,


      RECEIPT_DATA
      ================
      RECEIPT DATE,
      RECEIPT AMOUNT
      SALES_NUMBER
      SALES_DATE

       

      SAMPLE DATA FOR SALES_DATA
      ===================================
      SALES_DATE SALES_NUMBER SALES_AMOUNT
      01-JAN-2010 10001 2000
      03-JAN-2010 10002 3000
      15-FEB-2010 1003 2000

       

       


      SAMPLE DATA FOR RECEIPT_DATA
      ===================================
      RECEIPT_DATE RECEIPT_AMOUNT SALES_NUMBER SALES_DATE
      02-JAN-2010 1000 1001 01-JAN-2010
      22-JAN-2010 500 1001 01-JAN-2010
      28-JAN-2010 1500 10002 03-JAN-2010
      15-FEB-2010 1000 10002 03-JAN-2010
      28-FEB-2010 1000 1003 15-FEB-2010
      18-MAR-2010 500 1001 01-JAN-2010


      I want to measeure a metric known as Cash Collection. This is defined as all receipts from the selected
      period whose respective sales were made in the selected period.

      EXAMPLE:
      From the sample data above if the following selection is made
      Month: January
      Year: 2010

      The Cash Collection should be 3000( 1000 + 500 + 1500)

      ie all receipts collected in January and respective Sales were also made in January


      Month: JAN, FEB
      Year: 2010

      The Cash Collection should be 5000( 1000 + 500 + 1500 + 1000 + 1000)

      ie all receipts collected in January and February whose respective Sales were also made in January and February

      I want help on how best to model the data and if possible the kind of set analysis to use

      Your suggestions are welcome

       

        • Data Model and Set Analysis

          Hello,

          Your sample data is confusing (ie Sales_number 1001 and 10001; is it an error or your mind . To help you in an appropiate way: would you provide a little example application with your data. Use that little script as suggestion for your (corrected) data:

           

          Qualify *;
          SALES_DATA:
          Load * inline
          [SALES_DATE, SALES_NUMBER, SALES_AMOUNT
          01-JAN-2010, 10001, 2000
          03-JAN-2010, 10002, 3000
          15-FEB-2010, 1003, 2000
          ];

          RECEIPT_DATA:
          load * inline
          [RECEIPT_DATE, RECEIPT_AMOUNT, SALES_NUMBER, SALES_DATE
          02-JAN-2010, 1000, 1001, 01-JAN-2010
          22-JAN-2010, 500, 1001, 01-JAN-2010
          28-JAN-2010, 1500, 10002, 03-JAN-2010
          15-FEB-2010, 1000, 10002, 03-JAN-2010
          28-FEB-2010, 1000, 1003, 15-FEB-2010
          18-MAR-2010, 500, 1001, 01-JAN-2010
          ];


          Regards, Roland

            • Data Model and Set Analysis

              Hello

              I have corrected the data. I am currently using Evaluation Copy to present a demo.

              I want help on how best to model the data and if possible the kind of set analysis to use

              I have two tableS: SALES_DATA and RECEIPT_DATA with thier fields and sample data described below:

              SAMPLE DATA FOR SALES_DATA

              SALES_DATE

              SALES_NUMBER

              SALES_AMOUNT

              01-JAN-2010

              10001

              2000

              03-JAN-2010

              10002

              3000

              15-FEB-2010

              10003

              2000

               

              SAMPLE DATA FOR RECEIPT_DATA

              RECEIPT_DATE

              RECEIPT_AMOUNT

              SALES_NUMBER

              SALES_DATE

              02-JAN-2010

              1000

              10001

              01-JAN-2010

              22-JAN-2010

              500

              10001

              01-JAN-2010

              28-JAN-2010

              1500

              10002

              03-JAN-2010

              15-FEB-2010

              1000

              10002

              03-JAN-2010

              28-FEB-2010

              1000

              10003

              15-FEB-2010

              18-MAR-2010

              500

              10001

              01-JAN-2010

               

               

               

               

               

              I want to measeure a metric known as Cash Collection. This is defined as all receipts from the selected
              period whose respective sales were made in the selected period.

              EXAMPLE:
              From the sample data above if the following selection is made
              Month: January
              Year: 2010

              The Cash Collection should be 3000( 1000 + 500 + 1500)

              ie all receipts collected in January and respective Sales were also made in January


              Month: JAN, FEB
              Year: 2010

              The Cash Collection should be 5000( 1000 + 500 + 1500 + 1000 + 1000)

              ie all receipts collected in January and February whose respective Sales were also made in January and February

              I want help on how best to model the data and if possible the kind of set analysis to use