Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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:
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:
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.
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
Thanks for answering and bringing autonumber - ids into my mind.
Best Regards