Common (or CanonicalDate) for PurchasingDate & SalesDate

[did]

Hello Community

under the following url https://community.qlik.com/docs/DOC-5834 Rob Wunderlich makes usefull suggestions regarding how to link two or more dates from different fact tables.

My question is:

In the Rob's  example it is all about facts connected with OrderID which defines each fact date in one line. What if there are two fact tables (Purchasing and Sales) having their own OrderIDs (PurchaseOrderID and SalesOrderID)?

How in this case to connect these 2 fact tables and how to build a common calendar (as Rob does)?

DETAILS

I’ve created an app to reflect 2 scenarios (purchasing and sales) of AdventureWorks(2017)  database. For this purpose a common calendar for both fact tables is needed. For example, in order to put Total Sales (from Sales) and Total costs (from Purchasing) on the same time line.

The Problem is:

1. A. Products get bought within Purchase.Orders and then sold within Sales.Orders. One Purchase.Order can have one or many different products, same as one product can be bought within one or many Purchase.Orders. The same regards Sales. The costs and UnitPrices vary depending on Purchase.OrderID and Sales.orderID respectively.
2. B. There are 4 categories of products (apparel, accessoires, components, bikes), but only 3 of them get bought within Purchasing, whereas the 4th category of products (bike) gets manufactured by the company.

For this reason there is no field defining Purchasing and Sales at once. How to build a common calendar for both Facts as Rob suggests?

Kind regards

Denis

[ogautier62]

Hi

Can't you link first fact table with purchase date and second one with sales date?

Regards

[did]

This is exactly the problem: the only link between Purchase and Sales is Product, but there are products which never get bought from vendors (bikes) and products which never get sold to customers (components)

[jonathandienst · Accepted solution]

I would concatenate these two into a single fact table. Alias the PurchaseOrderID  and SalesOrderID to a common field OrderID, The same with the date fields and any other fields that are named differently but contain similar information. Add a derived TransactionType field set to 'Purchase' or 'Sales' to identify the type of transaction (which you can use in set expressions later in the front end).

Now if you have more than one date field (eg OrderDate, CompletedDate) you can use the canonical date propiosed by Rob, or here:Canonical Date

[ogautier62]

OK but you can still make a link with a common calendar table. I don't understand what is your problem in fact

Could you give an example?

[did]

Concatenation of two fact tables (Sales and Purchase) has helped, so that the common calendar (based on Commondate, which is the result of OrderDate and PurchaseDate) could be created and works fine.

Thank you jontydkpi