Two fact table

[krithikarees]

Hello All, I am stuck with a complex problem. Do any of you know how I can work my way around it?

I have SHIPDATE and RETDATE in two fact tables. I should create two different tables

1st table : eg: If panel SHIPDATE(Shipped) on 01-01-2017 and was RETDATE (returned) on 01-02-2017 count as 1 for the month of  return.

2nd table : eg: If a panel SHIPDATE(Shipped) on 01-01-2017 and was RETDATE(returned) on 01-02-2017 count 1 for the month of Shipment.

This needs to be done with the total of shipment and return remaining the same but bucketed differently in each table.

Example of what I have :

[felipedl]

Dont quite understand your requirements.

You have sales data, so you have the shipping and return date on the same data and data table or are they separate?

If you only need to count:

• numbers of shipped orders
• numbers of returned orders (meaning in this case they've been shipped at a given point)

You can simply create some flags with those rules and apply a simple sum on the created field to create your fact tables.

SalesDim:
Load SaleId,
Value,
Date#(CreatedDate,'DD/MM/YYYY') as CreatedDate,
Date#(ShipDate,'DD/MM/YYYY') as ShipDate,
Date#(ReturnDate,'DD/MM/YYYY') as ReturnDate,
DealerId;
Load * Inline
[
SaleId,Value,CreatedDate,ShipDate,ReturnDate,DealerId
1,10000,01/01/2018,05/01/2018,09/01/2018,A
2,500,05/01/2018,09/01/2018,,A
3,9994,08/08/2018,01/09/2018,24/09/2018,B
4,3555,25/09/2018,,,C
];


SalesFact:
Load SaleId,
if(len(ShipDate)>0,1,0) as [Order shipped flag],
if(len(ShipDate)>0 and len(ReturnDate)>0,1,0) as [Order Returned flag]
Resident SalesDim;

With that, you can check in a simple graph the sum of each flag to check your metric.

See the attached QVW for a simple solution with some dummy data.

In my example, I have 4 orders, 3 of which have been shipped and only 2 of those where returned.

Hope it helps.