Use Dynamic Formulas from Inline Table in Pivot Table

[#1]

Hello everybody,

i have a problem using dynamic formulas in a pivot table. (Qlikview 10)
I load the formulas in this way:

Standardreport:
LOAD * INLINE [SR_Item, SR_ItemFormula
Kontakte gemäß Händlerselektion, 'count(distinct{...)'
Geschäft nicht mehr existent, 'count(distinct{...)'
Nicht realisierte Kontakte, 'count(distinct{...)'
];

The formulas a included and are different for each row in the pivot table.



I insert SR_Item as Dimension and SR_ItemFormula as formula.

My problem:
Column Anzahl: =$(=SR_ItemFormula) is only working, when one formula is selected, otherwise there is a - sign.
Column Anzahl2: SR_ItemFormula: Qlikview is showing my formula as text, but is not calculating with it.

How to get work? Thanks for you help!

[hector]

Hi, did you try $(SR_ItemFormula) instead in the expression editor?

The problem of the null ( - ), is only because you are not selecting anything, so the value of the expression is NULL, maybe you can try a maxstring() or minstring() function but as always, it depends about your model

Rgds

[#3]

Hi Hector,

thanks for your fast answer. I tried $(SR_ItemFormula), but that is not working (like =$(=SR_ItemFormula)).
I am not sure if Qlikview is able to handle different formulas in one expression.

Regards, Martin

[hector]

Hi, qlikview can manage dynamic expression, the problem is you need to choose just one at the time

check this little test file, maybe it will help you

rgds

PS. For some reason, i'm unable to upload files with the .qvw extension, please rename the png for .qvw

[#5]

At first thanks for the time you spended for helping me.
The example file will not solve my problem - it is like you said: The problem is, that you need to choose one formula at the time.

In my case this means that qlikview is calculating all rows with the same (maxstring) formula.
But i need a different formula for each row, otherwise the result is wrong 😞

Perhaps it is possible to use dynamical dimensions and to implement the formulas from the inline table in these?

Regard, Martin

[Anonymous]

You can achieve something similar, not exactly what you want, using the pick in your expression. You will have to type all the formulas in the expression though.

Suppose you load your dimension with a number as well, so we can pick the right formula afterwards:

LOAD dual(SR_Item, SR_num) as SR_Item

INLINE

[SR_Item, SR_num

Kontakte gemäß Händlerselektion,  1

Geschäft nicht mehr existent,  2

Nicht realisierte Kontakte, 3

];

Now in your object expression, you can do a:

  = pick(SR_Item,

          'count(distinct{...)',

               'count(distinct{...)',

                    'count(distinct{...)', )

It will pick the right formula depending on the SR_Item in the dimension.

[#7]

Hi dj-kalle, Nicolai,

Nicolai is on the right track, but with a few minor issues:

- Use the 'SR_num' column in the pick() function; pick() expects a numeric value as its first parameter. (Update: just noticed that Nicolai uses dual() to stuff the number from 'SR_num' into the SR_Item field...)

- Don't put quotes around the various 'count(distinct ...)' functions, otherwise the result you'll get will be the formula text, instead of the value you want to calculate.

And ofcourse it's easier / more compact to use dollarsign expansion and the concat() function to generate the list of 'count()' alternatives. You'll end up with something like:

=pick(SR_num, $(=concat(SR_ItemFormula, ',', SR_num)))

which will evaluate to something like

=pick(SR_num, count({set_1} fields), count(...), ...)

with the various count() functions coming from the SR_ItemFormula field.

I'm passing the 'SR_num' field as an extra parameter to concat(), otherwise the SR_ItemFormula values might get sorted in the wrong order and you'd get the calculations mixed up (look up the 'sort weight' parameter for concat() in the QlikView help to see what I'm talking about).