Advanced Search in Set Analysis

[michael_klix]

Hi,

today I am stuck with a special set analysis. Out of a list of technical solutions, I have to pick the smallest version that just satisfies the demand. All this of course has to take place in a table inside the frontend, not in the script.

The following expression works and delivers the minimum solution that has a capacity >100:

min({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)},SOLUTION_DL_CAPACITY_cumulative={">100"}>} SOLUTION_PRIO)

Unfortunately, the use in a table requires that the formula is evaluated individually in each row of the table where the value "100" has to be replaced by a formula that delivers the specific demand for that row of data. This demand is represented by a quite complex formula, containing again set analysis. But my problem starts much earlier:

I cannot get the advanced search in the set analysis work fith data fields. I managed to create an expression, that at least accepts a calculation. I replaced 100 by $(=1000/10) and it worked. So I know how to use $(=...) with equal sign to get a calculation working.

min({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)},SOLUTION_DL_CAPACITY_cumulative={">$(=1000/10)"}>} SOLUTION_PRIO)

But as soon as I use a fieldname inside the search, I get a NULL as result of the expression.

How can I get someting like this to work (linefeeds just for easy reading), where a DEMAND is multiplied by the correct GROWTH?

min({<
Bearer={'4G'},
YEAR_EVOLUTION_PATH={$(vYear)},
SOLUTION_DL_CAPACITY_cumulative=
     {">$(=DEMAND*only({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)}<} GROWTH) )"}
          >} SOLUTION_PRIO)

Any help is appreciated...

[oskarwila]

Hola Michael,

Si la formula que propones devuelve null tendría en cuenta varias opciones:

* La fórmula es incorrecta, igual es al escribirla aquí en el foro

only({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)}<} 

only({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)}>} 

¿Está fórmula en la tabla como medida devuelve un único dato?

*Si la primera opción es correcta 

 {">$(=DEMAND*$(=only({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)}>}GROWTH) )"}

Supongo que ya lo habrás probado pero a simple vista es lo primero que probaría.

Saludos

[michael_klix]

Hola y muchas gracias,

actually I tried all expressions inside the set analysis before and they are all working inside the table. Actually especially the field "DEMAND" is unique per line in the table and even the easiest calculation
SOLUTION_DL_CAPACITY_cumulative={">$(=DEMAND)"}
Or
SOLUTION_DL_CAPACITY_cumulative={">$(=only(DEMAND)"}
Or
SOLUTION_DL_CAPACITY_cumulative={">$(= only({} DEMAND)"}

Do not work, despite each of the expressions
=DEMAND
=only(DEMAND)
=only({} DEMAND)

Deliver a value per line in the table.

Interesting side effect:
In one version, I made a syntax mistake in the last set analysis to filter the solutions by their cumulative capacity. In this case it did not deliver NULL, but simply the min of ALL solutions, ignoring the set analysis:
{">$(only({} DEMAND) )"}
inside Set analysis delivers NULL, while
{">$(only({Delivers 1 (the real minimum of all solutions (they have IDs from 1 to x)

[marcus_sommer]

You could try it in this way:

min({<
Bearer={'4G'},
YEAR_EVOLUTION_PATH={$(vYear)},
SOLUTION_PRIO =
     {"=SOLUTION_DL_CAPACITY_cumulative>DEMAND*only({<Bearer={'4G'},YEAR_EVOLUTION_PATH={$(vYear)}>} GROWTH)"}
          >} SOLUTION_PRIO)

- Marcus

[michael_klix]

Unfortunately no result either.
I simplified the approach to make it comparable with something that worked:
SOLUTION_PRIO={"=SOLUTION_DL_CAPACITY_cumulative>100"} or
SOLUTION_PRIO={"=SOLUTION_DL_CAPACITY_cumulative>$(=100)"}
but it did not work, while the set analysis
SOLUTION_DL_CAPACITY_cumulative={">$(=100)"} or
SOLUTION_PRIO={"=SOLUTION_PRIO>3"}

at least result in something different to NULL. So even though I can follow the idea to make the advanced search a "real" comparison expression by using SOLUTION_PRIO= instead of SOLUTION_DL_CAPACITY_cumulative=, I wonder why the comparison with a number does not work.

[marcus_sommer · Accepted solution]

With a "classical" set analysis it's not possible to consider fieldvalues and/or expression-results on the row-level of an object. The reason is that a set analysis worked like a selection - which means it will be evaluated against the datamodel and not against the dimension-values within the object. And it happens before the object is calculated - the selection respectively the set analysis condition determines which (connected) fieldvalues are available and on this remaining dataset will the calculation be performed.

Also this couldn't be bypassed through the use of $(=...) which creates an adhoc-variable which is calculated once before the chart is calculated and the result of it is then applied to all rows.

Therefore my suggestion not to use the $(=...) within the condition else to use there a different kind of comparing like:

"=Field1>Field2" respectively "=Field>AnyExpressionResult"

which is rather a comparing like a check within an if-loop. Depending on the datamodel it worked in some cases - you may try it again with another target-field. If this don't work you will probably need a different approach to get your wanted results.

Thinkable approaches could be to use a pick(match()) with multiple specialized expressions, maybe also to transfer some kind of pick(match()) within the set analysis, using some aggr() to calculate everything in virtual table and picking then from there the needed parts or to split your expressions into two parts, like:

YourExpressionWithSetAnalysisButWithoutTheCriticalCondition * if(TheCriticalCondition, 1, 0)

Personally I would look to the last suggestion at first, then my first and the second/third are more theoretically because they could be become quite complex and are if they work probably very slow.

- Marcus

[michael_klix]

Hi Marcus,

that was the elementary hint: No Value inside Set Analysis that depends on the table's row.

• The pick-match did not work as I need the condition inside a set anlysis to find the prio number that would give the pick
• The next option (YourExpressionWithSetAnalysisButWithoutTheCriticalCondition * if(TheCriticalCondition, 1, 0)) did not work either, but brought me closer

The solution is now not very elegant, but it works:

= if (my-linebased-threshold < only({<SOLUTION_PRIO={1}, my-set-analysis>} SOLUTION_DL_CAPACITY_cumulative), only({<SOLUTION_PRIO={1},my-set-analysis>} SOLUTION_NAME),
//else

if (my-linebased-threshold < only({<SOLUTION_PRIO={2}, my-set-analysis>} SOLUTION_DL_CAPACITY_cumulative), only({<SOLUTION_PRIO={2},my-set-analysis>} SOLUTION_NAME),
//else

....

if (my-linebased-threshold < only({<SOLUTION_PRIO={15}, my-set-analysis>} SOLUTION_DL_CAPACITY_cumulative), only({<SOLUTION_PRIO={15},my-set-analysis>} SOLUTION_NAME),
//default else
'no solution found'
)
...
...
)
)

The downside if this hardcoded if-loop is that I have to anticipate how many potential solutions we will have. But I might prepare it even for 15 or so assuming that

• a) we will never have more then 15 potential solutions
• b) it will not make the loop slower because it stops anyhow  as soon as the condition is true, no matter how much follows. Only the very rows that do not fulfill any of the conditions ( getting default 'else') will go through all if's.

Is my assumption b) correct?

Or is there even a better way visible?

Thanks for the fast reply. You made my day!

keep qliking
Michael

[marcus_sommer]

Such nested if-loop mustn't be mandatory static with n fixed loops else it might be calculated but it's definitely not an easy task. The bad message is that if fixed or dynamic in some way all branches will be at first completely calculated and afterwards the logic picks the first TRUE from the results. It's an intended behaviour which has some benefits but if it comes to really many nested loops and/or to the fact the the included calculations are quite heavy it becomes a disadvantage.

AFAIK the use of pick(match()) behaved quite similar from a performance point of view because match() worked with the same logic like the if-loop (it's just a different (better) kind of the syntax). But only with pick() it may be different. The challenge here is to calculate the pick-index. I don't remember a definite case but I had it already done. Here I could imagine a logic that your threshold field is extended to a parallel counter-field maybe created with recno/rowno/autonumber which could then be used as pick-index.

Another approach might be to pre-calculate all those values within a variable, like:

concat(aggr(YourExpression, YourDimensions), '|', Sorting)

and then using functions like subfield() to pick the appropriate value from the string-result of the variable. With it you won't save efforts in regard to create this expressions with the mentioned nested if-loops or maybe a pick(match()) but it minimized the heaviness of the calculations within the object because there it's just picking a string-part and comparing it against a fieldvalue which should be quite fast even by a lot of nestings. The essential point is if the single calculation of the aggr-construct in the variable is more performant as multiple simple aggregations - without the aggr() - in the chart.

- Marcus