Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Quy_Nguyen
Specialist
Specialist

QlikSense - Set Analysis for intersection of 2 data sets

Hello,

I am create a report table with this data model:

Quy_Nguyen_0-1694097717096.png

In this data model, I have order and factory information linked together:

+ Order: order data with vendor and factory information

+ Factory: dimension for factory information

+ FEM: yearly score for factory

+ Factory_Link: use to link Order, Factory and FEM together using Key combined by Factory_Code and Year

I need a measure that calculates the total FEM score for each Vendor. It is sum of FEM scores (last year) for all factories that have orders in the selected period.

For example, with this data set:

Quy_Nguyen_1-1694098451555.png

I select the period from Mar-23 to Aug-23, the score for Vendor 1 (factory A,B) last year is 8 and Vendor 2(factory A,B,C) is 13, but in the selected period, Vendor 1 just has 1 order with factory A, the score for Vendor 1 should be 4, Vendor 2 has orders with factory A,B,C, the score is still 13.

Quy_Nguyen_2-1694098746825.png

My measures definition:

+ FEM Last year:  

Sum({<MonthStart= , Year_FEM = {"$(=Max(Year(MonthStart)-1))"}>}Score)

+ FEM Last year for Existing Order (what is not working):

 Sum({<MonthStart=, Year_FEM = {"$(=Max(Year(MonthStart)-1))"}, Factory_Code = P({<Req_Ship_Date={">=$(=Min(MonthStart))<=$(=Max(MonthStart))"}>})>} Score)

I also attached a QVF file and data for the example above.

Any help would be much appreciated!

 

Best, 

Quy

 

 

 

 

Labels (2)
1 Solution

Accepted Solutions
marcus_sommer

I think your data-model isn't really suitable for the intended view because your factory-key doesn't include the needed period-information.

A solution for your scenario may be to concatenate the orders and the scores. With it the period-handling would be quite easy - the score-year could become a date maybe per makedate(YEAR_FEM). Other things might be more difficult but in my experience such concatenated fact-table is nearly always the simplest approach.

View solution in original post

5 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Quy,

I tried opening your sample app, but it wouldn't open in my QSD...

Anyway, the condition for the last year's orders, that fit within the selected range, should be based on the Date and not on the year. In a nutshell, schematically, the condition should read:

Date >= AddMonths(MinDate, -12)<=AddMonths(MaxDate, -12)

This way, you are selecting corresponding dates and not years. Also, if you want to disregard the selection of MonthStart, you should place that filter at the end of your Set Analysis, because filters are applied sequentially. Once you disregard the selections in a field, you can't refer to them again in the same Set Analysis.

To learn more advanced development techniques, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Quy_Nguyen
Specialist
Specialist
Author

Hi Oleg,

Thanks for your quick response.

The Year_FEM field is in FEM table and it linked to Order by the Key Factory_Code + Year. So I think it is both ok if I filter by Year_FEM or by Date field. Anyway, I tried changing it but it doesn't work.

I moved the MonthStart= to the end but it returned the same result.

My qvf file created from May 2022 patch 4. Could you please help me by loading the source file into a new application? All tables were structured, you just need to load them all. Thanks alot!

best, 

Quy

 

Quy_Nguyen
Specialist
Specialist
Author

hello community, I still need your support on this topic 😞

marcus_sommer

I think your data-model isn't really suitable for the intended view because your factory-key doesn't include the needed period-information.

A solution for your scenario may be to concatenate the orders and the scores. With it the period-handling would be quite easy - the score-year could become a date maybe per makedate(YEAR_FEM). Other things might be more difficult but in my experience such concatenated fact-table is nearly always the simplest approach.

Quy_Nguyen
Specialist
Specialist
Author

Thank you so much Marcus!

Yeah I was too focused on how to achieve it using set analysis. 

I changed the data model and now it works.

Best,

Quy