topic Re: How to connect different Dates in Set Analysis in App Development

How to connect different Dates in Set Analysis

Anonymous — Sat, 16 Nov 2024 18:02:33 GMT

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!

Re: How to connect different Dates in Set Analysis

Taoufiq_Zarra — Tue, 29 Sep 2020 14:05:31 GMT

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:

Re: How to connect different Dates in Set Analysis

dadumas — Tue, 29 Sep 2020 15:58:49 GMT

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.

Re: How to connect different Dates in Set Analysis

Anonymous — Wed, 30 Sep 2020 07:18:07 GMT

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

Re: How to connect different Dates in Set Analysis

Anonymous — Wed, 30 Sep 2020 07:18:51 GMT

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

Best Regards