topic Use Dynamic Formulas from Inline Table in Pivot Table in QlikView

Use Dynamic Formulas from Inline Table in Pivot Table

Thu, 06 Jan 2011 12:34:54 GMT

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!

Use Dynamic Formulas from Inline Table in Pivot Table

hector — Thu, 06 Jan 2011 13:40:01 GMT

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

Use Dynamic Formulas from Inline Table in Pivot Table

Thu, 06 Jan 2011 14:17:26 GMT

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

Use Dynamic Formulas from Inline Table in Pivot Table

hector — Thu, 06 Jan 2011 14:32:52 GMT

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

Use Dynamic Formulas from Inline Table in Pivot Table

Thu, 06 Jan 2011 16:18:19 GMT

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

Use Dynamic Formulas from Inline Table in Pivot Table

Anonymous — Thu, 23 Jun 2011 12:36:36 GMT

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{...)', )

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

Use Dynamic Formulas from Inline Table in Pivot Table

Tue, 20 Dec 2011 11:33:12 GMT

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).