QlikSense - Set Analysis for intersection of 2 data sets

[Quy_Nguyen]

Hello,

I am create a report table with this data model:

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:

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.

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

[Oleg_Troyansky]

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]

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]

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

[marcus_sommer · Accepted solution]

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]

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