How to connect different Dates in Set Analysis

[Anonymous]

Hi, 

I have the following fields and tables:

Orders:

OrderNr | OrderDate | Article | OrderAmount

Sales:

SalesDate | Article | SaleAmount

__

In Set Analysis i want to have following table (filter on one OrderNr):

Article | OrderAmount | SalesAmount(Set Analysis: SalesDate = between OrderDate and today)

Thanks alot!

[Taoufiq_Zarra · Accepted solution]

hi @Anonymous 

I guess that in the Sales table you have a unique identifier for each sale, otherwise I created one as shown in the script below :

Orders:

load * inline [
OrderNr,OrderDate,Article,OrderAmount
1,01/01/2020,a,100
2,20/12/2020,b,200
];

Sales:

load *,autonumber(SalesDate&Article) as IdSales inline [
SalesDate,Article,SaleAmoun
01/02/2020,a,50
01/04/2020,a,60
01/10/2021,a,40
22/12/2020,b,60
]

then u can create  your Set script as :

=sum({<IdSales={"= num(today()-SalesDate)>=0 and num(OrderDate-SalesDate)<=0 "}>}SaleAmoun)

output:

[dadumas]

I would concatenate Orders and Sales into 1 table called Fact_Main, and use a common date column. 

Pro Tip:  always (100% without exception), concatenate transaction (fact) tables into a single concatenated fact.  

Then build dimensions around the concatenated fact.  I have probably built at least 100 QV data models this way.  

Then use a common name for the date like transactionDate  Then, transaction date can be filtered and acrosss the 2 types of transactions.

[Anonymous]

Thanks alot for answering.

My description didnt contain any information if the tables are concatenated or not. 😉

Im also building all my apps with the star schema data model, but in this case, i did not get the analysis-table i wanted.

Again, thanks for answering!

Best Regards

[Anonymous]

Thanks for answering and bringing autonumber - ids into my mind.

Best Regards