Using contents of (a non selected) Field as another Field

[#1]

Hello all,

QV9 User.

I have a financial report where I have calculations to be used in a ratio, the ratio divisor being specific rows in the report, not one divisor per column. I have the report working successfully using a series of IF statements, but I want to replace that with a more efficient and speedy calculation, removing the IF conditions.

[BUD01.Divisor] is a field that contains the names of several other fields.

if( [BUD01.Divisor] ='NETSALES' ,    

// sum Net Sales

sum(TOTAL PeriodBalance * NETSALES)),

if( [BUD01.Divisor] ='WHS' ,    

// sum WH Sales

sum(TOTAL PeriodBalance * WHS)),

if( [BUD01.Divisor] ='EXP' ,    

// sum WH Sales

sum(TOTAL PeriodBalance * EXP)),

AND SO ON for each BUD01.Divisor value in the report.

After looking on the forum, I have tried a few solutions. Basically I want the fieldname in BUD01.Divisor to act as field in itself.

I thought this might work

sum(TOTAL PeriodBalance * $(=([BUD01.Divisor]))

(this only works, say for NETSALES, if NETSALES is selected, but that is no good for any of the other divisor values).

Has anyone any suggestions on this, or do I need to add logic to the script to handle this.

Many thanks for any help.

[Anonymous]

Hi,

You could create a variable, for example vDivisor, defined as: =getfieldselections([BUD01.Divisor])

When you select a single value in the BUD01.Divisor field the variable will change to that field value, so you could write your expression as:

sum(TOTAL PeriodBalance * $(vDivisor))

Just tested on version 11 but should be fine for v9 too.

[#3]

Thanks very much for the reply, Johannes

That works when a value in BUD01.Divisor is selected, but we can't do that since it is used in a column in a 40+ line report (Straight Table), with each line having a potentially different Divisor. 

I'm attaching a sample of the report, adding BUD01.Divisor and BUD01.Divisor Amount for illustration.

Thanks for any help.

Mea

[vincent_ardiet]

Hi,

The problem is that when you use the variable expansion ($(=....)) QV looses the context.

Try to put ='$(=concat(BUD01.Divisor))' in an expression and you will see that QV concats all divisor not only the current one.

And unfortunately the Evaluate function works only in script.

I will follow the subject because I'm curious of a solution but I have not a lot of hope.

Regards,

Vincent

[#5]

Thanks Vincent,

as you say the '$(=concat(BUD01.Divisor))'  logic brings back all values in QV concats all divisors, when we only want the one in context for each line.

Hoping someone will have a solution for us.

Many thanks

Mea.