Set Analysis with field less than an expression

[pattyccia]

Hi to all,

I have following data in my data model:

Every single Product have a keyrow (rownumber by script) and every single Customer has goals (obj).

I would like input a variable in % to have following results in a QS table:

For every Customer i have to calculate obj + % defined in variable and then calculate for each individual customer the sum of the amounts up to the keynum with this number (obj + %).

The last column in the table is the one I can't calculate.

I tried with:

1) Sum({< keyrow={"<=$(Max(obj_cpo_field)* vPerc)"} >}Amount)

2) Sum({< keyrow={"<=$(=Max(obj_cpo_field)* vPerc)"} >}Amount)

but doesn't work.

Any help is appreciated.

Thanks,

Patrizia

[Oleg_Troyansky · Accepted solution]

Hi Patrizia,

There is a number of fundamental issues here, that prevent this approach from working. 

First, Set Analysis conditions are evaluated once per dataset (not in every line of the chart), and therefore they cannot be sensitive to specific dimensions or measure values in your chart.

An advanced form of a Set Analysis search, the expression search, could allow you to perform this calculation at a single field level - in this case, you'd need a single field that combines both the Customer number and the Keyrow number. Then, you could formulate an expression search that would select those combinations of Customers/Keyrow numbers that fulfill your condition.

So, I see two possible solutions here:

1. If your data is small enough, you can use a simple IF condition to select the relevant lines, like this:

Sum(   IF( keyrow<=obj_cpo_field*(1+ vPerc), Amount) )

2. If you really want to solve this in Set Analysis, then add a data field that combines the Customer field and the keyrow field (let's call it CustRow) and then the condition would look like this:

Sum({< CustRow={"=keyrow<=Max(obj_cpo_field)* (1+$(vPerc))"} >} Amount)

Frankly, this might be even slower than the IF condition, because this expression search has to evaluate every single line of data. So, in this case, the simpler IF solution could be preferable.

Keep in mind, that I'm typing it from memory, so you may need to tweak the syntax a bit. Also, I'm assuming that vPerc is formatted as a percentage, so the actual value for 50% is 0.5 (verify it in your app).

Cheers,

[Dolphin]

You have to use the ID instead of ROW, as the search will otherwise be done on ROW-Level and one ROW Value may have multiple OBJ Values (ROW = 1 has Obj values {2, 10, 1, 8}). In my example ROW = 3 is unique and the search can be calculated strictly for that case.

Sum({< ID = {"= ROW <= OBJ *1.5"}>} VAL)

[pattyccia]

Thank you Oleg, the solution 1 with sum(if...  works perfectly.

Thank you also for the explanation about the logic of set analysis, it's very clear!

[pattyccia]

Thank you Dolphin, I tried also your solution but if if I replace the variable instead of 1.5 in the expression, and change the value of the variable, it does not work.

I don’t know if I’m wrong about syntax.

Thanks anyway for your time.

Patrizia

[Dolphin]

Syntax for the variable would use the $-Sign expansion:

Sum({< ID = {"= ROW <= OBJ * (1+ $(vPerc))"}>} VAL)

As for performance, there are a lot of discussions around. Usually Set Expression wins the race.

E.g. If statement vs set analysis - Qlik Community - 591661

While the set expression - in this case - would basically calculate row wise, the if condition does too. The only difference is, that the set is calculated "in one go" to first reduce the set of data to do the actual calculation on and the if condition is calculated for each row separately. At least that's my understanding. Probably not much of a difference for your case anyway.