Use a Formula loaded from database

[markuskoessler]

Hi veryone,

is it possible to use a formula in a pivot table which was loaded from database?

I've loaded a table from our database which looks like that

those formulas work if I use them directly in my pivot table, but not if use the field which stores the formulas,

I'm ending with something like that

Within the pivot table I've several If-Statements like 

if(SPALTEBVBEZEICHNUNG='Vorjahr',
if(SPALTEBVID=1,KENNZAHL_KONTAKTE_VJ,

...

where KENNZAHL_KONTAKTE_VJ is one of my fields storing a formula.

I've tried to write it like '$(=KENNZAHL_KONTAKTE_VJ)' or =$(=KENNZAHL_KONTAKTE_VJ) but i just don't get it.

Maybe someone has a hint for me?

Thanks!

Markus

[marcus_sommer]

What you are trying isn't possible because a field-content is always a number or a string and won't be interpreted as expression. If you apply something like: $(=Field) you creates an adhoc-variable which is resolved before the chart is calculated and applied to each row - means if you has always only a single calculation it would be applicable but not if you want different calculations in regard to the dimension-values.

By different calculations to the dimensions you will need nested if-loops, like:

if(Dim = 'x', Expr1, if(Dim = 'y', Expr2, ...)

That's technically possible but a rather ugly approach and not very performant because all branches within the if-loop will be calculated at first and picking the right then/else result is the second step. From a handling point of view the nested if-loop might be optimized by using a pick(match()) in which lookup and returns are aggregated strings over the expressions but it will cause further complexity.

Better as the above stuff would be to adjust the data-model and to associate all data properly which means to move the logic from the expressions to the dimensional layer and then simple sum() and count() in the objects will be enough.

[markuskoessler]

Thank you for your reply, I'm gonna try that.