Set Analysis

[SonPhanHumanIT]

Hello Qlik Experts,

I'm currently working on a formula that I'd like to optimize using Set Analysis for greater efficiency. However, I'm not achieving the desired results.

The current formula reads as follows:

My objective is to restructure it somewhat like this: 

But this doesnt work...

I would greatly appreciate your assistance or any advice on the best approach to tackle this.

Thank you in advance!

Best regards Son

[Mark_Little]

Normally when i look at optimizing like this, I would start by looking at the script and moving the heavy lifting to the back end.

[SonPhanHumanIT]

@Mark_Little 

Thank you for your advice. Yes, I agree with you on that. However, I also see this as an opportunity for me to practice how to write such lines of code using Set Analysis. It's a sort of exercise for me to improve my skills in that area.

[Javizh · Accepted solution]

Hello @SonPhanHumanIT 

In order to do it purely on set analysis, you will probably need to calculate a field in the script consisting on the 3 fields you are agregating by:

• LinkField =  S_Artiker.Artikel&'|'&ML_Ort.LagerOrt&'|'&S_Firma.Firma

Then you can use the set analisys on that new field.

• count(distinct {<pA_stock.Date....   LinkField = {" YourCondition "}    >}LinkField)

However, dont expand with "$()"YourCondition, as it has to be calculated for each LinkField. If you expand it you will only get a single value, and you need one for each LinkField

Hope this can help.

Best regards.

[marcus_sommer]

I think your calculation couldn't be further optimized with an expression - at least not if your data-model is forcing such an approach. That's caused from the fact that you compares the result of two sub-calculations within a quite granular dimensional context.

The aggr() creates a virtual table on an article + stock + company level which becomes probably quite large and on this are then the two sub-expressions calculated and compared which is then summed in the context of the parent-object. IMO there is no real possibility to optimize it.

Therefore if you want to improve the performance you will need to apply some changes to the data-model - maybe be adding the safety-stock information to the current-stock information and subtracting them there twice - directly to get the real differences and a second time to flag the direction, maybe like:

pick(sign([safety-stock] - [current-stock]) + 2, 'pos', 'zero', 'neg') as StockFlag

This flag could then be used as selection and/or dimension and as expression you may use something like:

count(distinct article&lager)

depending on the view-requirements. I would expect that such an approach would speed up the UI very significantly.