Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
karl
Contributor III
Contributor III

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 @karl 

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 @karl 

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.

karl
Contributor III
Contributor III
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

karl
Contributor III
Contributor III
Author

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


Best Regards