topic Re: Use a Formula loaded from database in App Development

Use a Formula loaded from database

markuskoessler — Wed, 27 Mar 2024 14:25:24 GMT

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

Re: Use a Formula loaded from database

marcus_sommer — Wed, 27 Mar 2024 16:49:24 GMT

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.

Re: Use a Formula loaded from database

markuskoessler — Thu, 28 Mar 2024 05:46:21 GMT

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