Calculation with text output

[Anonymous]

Hello,

I've about 100 formulas, that will be calculated in the script. These are all simple divisions. This works fine, if I only need the final result.

Now I want have the absolute values of the numerator and denominator too and want to show this in a diagram-table. So I split the calculation in a numerator-variable and a denominator-variable. Then I made a function, where I can set the formula-name as parameter. This way I keep the formulas in the diagram simple and can make centralized changes in the script.

Example (val1 and val2 comes from database in real life)

set val1=100;

set val2=25;

set formula1="sum(val1) / sum(val2)";

set formula2_num="sum(val1)";

set fomula2_denum="sum(val2)";

set divide=$1 & '_num / ' & $1 & '_denum';

In the diagram I have the formula-definition "$(=$(divide('formula2')))".

This works fine and I get the result "4" in my table.

But I couldn't find a way to display the absolute values in die diagram-table. I want a parameter-function like "divide" in the example, so I can easely change the way the values are display.

What I would like to see is "100 / 25" or even better "100 / 25 = 4".

I couldn't find a way to substitude $1&'_num' with 100, but not calculte the division. I want QlikView to recognize the dash as pure text. If I use "$(divide('formula2'))" in the formula-definition, I see "formula2_num / formula2_denum", but the names are not substituted by the value.

Does anyone have an idea how to solve this?

[swuehl]

Have you tried something like

$(='$(divide('formula2'))' & ' = ' & ($(divide('formula2'))) )

[kuba_michalik]

For the life of me, I cannot get your original "divide" variable to work as a formula (that's in QV 12.10). I have no idea why it works for you, because it shouldn't - it first resolves to formula2_num / formula2_denum, but in the next step such "naked" variable references resolve to their text value as far as I know, so it will be dividing text by text (which of course does not work).

However, I managed to solve the whole problem by setting "divide" to:

$(=$($1_num)) / $(=$($1_denum))

and the final formula to:

='$(divide(formula2)) = $(=$(divide(formula2)))'

Of course, to avoid repetition, you can wrap it in another variable, like divide_outer:

$(divide($1)) = $(=$(divide($1)))

and the in formulas use:

='$(divide_outer(formula2))'

It works, and I find it cleaner because it's pure $ expansion, without adding string concatenation to the mix. In my experience, mixing $ and quotes usually leads to headache

Trouble is, you cannot set variables like my version of "divide" by SET statement, because it will try to resolve inner $ references immediately and break everything. You can create them manually in Variable Overview, or use a workaround like this:

let divide=Replace('$ (=$ ($ 1_num)) / $ (=$ ($ 1_denum))','$ ','$');

[marcus_sommer]

I want say that your approach wouldn't work but I think it's too complicated as it would be useful. The work with variables should be simplifying (developing, overview, maintaining, ...) things and not adding more complexity.

Therefore I suggest a different approach like the following:

load * inline [

id, val1, val2

1, 100, 25

2, 50, 5

3, 10, 10

];

set divide = dual(sum($1) & ' / ' & sum($2) & ' = ' & sum($1) / sum($2), sum($1) / sum($2));

and as expression:

$(divide(val1, val2))

with result of:

- Marcus

[marcus_sommer]

You could avoid the $-sign problem by creating variables within the script if you load the variables as fieldvalues and assigns those fieldvalues to variables. Here you will find various examples to this and some more useful informations about Variables.

- Marcus

[kuba_michalik]

Sure. For this example, I worked under assumption that variables are to be assigned in script, not from data (sometimes if it's just a couple of variables, creating extra data source for managing variables is overkill, although I agree if there are many, it's totally worth the effort)

[Anonymous]

So I'm back in office today and with your help, I found the solution.

To calculate and display the absolute values I have:

set absolut_def="dual(~(~1_num) & '/' & num(~(~1_denum)) & '=' & num((~(~1_num))/(~(~1_denum)),'0,00%'),(~(~1_num))/(~(~1_denum)))";

set absolut = "=replace([absolut_def], '~','$')";

In the diagram I use the formula:

=$(absolut(formula2))

So your solution with "dual" and "replace" helped a lot.

To display only the resulting value, I use a second function:

set quotient_def="(~(~1_num)) / (~(~1_denum))";

set quotient = "=replace([quotient_def], '~','$')";

So for the absolute value I see "100/25=4" in the diagram and for the resulting value only "4".

Thank you all very, very, very much!!!