Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to connect different Dates in Set Analysis

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!

Labels (2)
1 Solution

Accepted Solutions
Taoufiq_Zarra

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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

4 Replies
Taoufiq_Zarra

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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
dadumas
Creator II
Creator II

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
Not applicable
Author

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
Not applicable
Author

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


Best Regards