Data Model and Set Analysis

[#1]

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

[#2]

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

[#3]

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

[#4]

OK,

take a glance at my example application. There you can see a simple data modell. I did not use any set analysis because it wasn't necessary. I didn't know what to do with the Sales_Amount, should it be a simple sum of Receipt_Amount ?

Have fun while exploring the exam app.

RR

[#5]

Thanks for your solution. But it is not giving me what I am expecting.

From the above image if the month Feb is selected I am expecting to have receipt which were collected in February and with their respective Sales also made in February. The current solution gives me all receipts made in February without any regards to when the sales were made.

Hope to hear from you.

With Kind Regards

Abraham

[#6]

Hello Abraham,

now I know what to do with your Sales_Amount. Take a look at my app Version 2.0.

RR