Subtraction of two SET Variables

[kvr9]

Hello,

I'm stuck with an issue with subtraction. My data contain two variables was trying to subtract them but it is not working properly

Set vCountry=1,

Set vValue =if(vCountry =1,Localcurrency, Localcurrency*exchangerate)

Set vSale =if(vCountry=1,Sales,Sales*exchangerate)

above is the variables I have created for my data and was trying to subtract them as below.

vValue -vSale

I am getting result for local values but not the converted values i.e if country is 0 or more than 1 then it should convert using exchange rates but it is not working as expected

Can some one help me out.

Thanks.

[Peter_Cammaert]

Are you saying that you have a problem with putting this expression in the variable so that the intended $-sign substitutions survive the execution of your script ?

Mind you, if this was the expression all along, then please post it at once. Your simplified example doesn't contain any $-sign substitutions at all, thereby possibly eliminating the issue you are facing...

[Peter_Cammaert]

Please post the actual SET statements of the variables you use in an object to assemble the expression. Ok, they may not be working now, but what do they look like?

[kvr9]

Hi Peter,

Please find the set variables statements.

[Peter_Cammaert]

Ok, let's continue with a picture of the SET statements. Next time please post them as text so that we can reuse them in our own tests.

Do you realize that if you say for example

=Sum($(vValueField))

in an object expression, that you will be trying to calculate a sum() of a series of strings?

Quoted items are recognized as string constants and not as the fields they represent. Why do you quote your field names?

Also be aware that your expressions carry an initial equal sign. That means that in the expression I mentioned above, not the content of the variable will be used, but the result of evaluation of the content. Since $-sign substitution in object expressions is performed before the object is recalculated, you will not get a single row-dependent value but all field values in each IF(). Which commonly evaluates as NULL.

So try to remove the quotes and the equal signs in front of the SET expressions and try again with $-sign substitution in your expressions, instead of fully specifying them in each object.

For the vNumberFormat variable you did the inverse with respect to quotes and strings, and it may work but that depends on whether DivisionFormat is a field (or a variable) with a single value or with multiple values.

[kvr9]

Hi Peter,

Thank you for your response.

Apologies for sharing the expression in picture format.

Please find the expressions :

I am trying to use the below expression but it is not working as expected:

=num(SUM({<Year={$(=max(Year))},[Payment Type]={'R&D'}>}$(vDvalueField)/UnitDivision)-sum({<Year={$(=max(Year))},[Payment Type]={'R&D'}>}$(vValueField)/UnitDivision),'$(vNumberFormat)')

So replaced the varaiables with expression:

=num(SUM({<Year={$(=max(Year))},[Payment Type]={'R&D'}>}if($(vLocalValue) =1,(DisclosedToV*ExchangeRateToUSD), DisclosedToV)/UnitDivision)-sum({<Year={$(=max(Year))},[Payment Type]={'R&D'}>} if($(vLocalValue) =1, SpendAmount, (SpendAmount/ExchangeRateToUSD))/UnitDivision),'$(vNumberFormat)')

The issues is at conversion i.e the IF condition is not calculating as expected when we use a varaiable.

Please find the set expressions:

Set vLocalValue = 0;

Set vValueField = =if(vLocalValue=1, '[SpendAmount]', '[SpendAmount]/[ExchangeRateToUSD]');

Set vDisclosedToV = =if(vLocalValue=1,'[DisclosedToV]*[ExchangeRateToUSD]', '[DisclosedToV]');

Set vNumberFormat = =if(vLocalValue=0, DivisionFormat, Replace(DivisionFormat,'$',''));

I was unable to get your point,request you to share the test qvw file.

[kvr9]

the individual variables are working as expected

=num(sum({<Year={$(=max(Year))}>}$(vDisclosedToV)/UnitDivision),'$(vNumberFormat)')

=num(sum({<Year={$(=max(Year))}>} $(vValueField)/UnitDivision),'$(vNumberFormat)')

but while I'm trying to  subtract it is not working(calculating) for conversions

[Anil_Babu_Samineni]

Image please? I would expect 3 images which are 2 images for different variables in diff. formats and then show us with Subtract data with formulae too

[kvr9]

Hi Anil,

The 1st image is for two expressions one of the expression is commented.

1st expression=num(SUM({<Year={$(=max(Year))},[Payment Type]={'R&D'}>}$(vDvalueField)/UnitDivision)-sum({<Year={$(=max(Year))},[Payment Type]={'R&D'}>}$(vValueField)/UnitDivision),'$(vNumberFormat)')

2nd expression

=num(SUM({<Year={$(=max(Year))},[Payment Type]={'R&D'}>}if($(vLocalValue) =1,(DisclosedToV*ExchangeRateToUSD), DisclosedToV)/UnitDivision)-sum({<Year={$(=max(Year))},[Payment Type]={'R&D'}>} if($(vLocalValue) =1, SpendAmount, (SpendAmount/ExchangeRateToUSD))/UnitDivision),'$(vNumberFormat)')

2nd is for Set variables

Please find the set expressions:

Set vLocalValue = 0;

Set vValueField = =if(vLocalValue=1, '[SpendAmount]', '[SpendAmount]/[ExchangeRateToUSD]');

Set vDisclosedToV = =if(vLocalValue=1,'[DisclosedToV]*[ExchangeRateToUSD]', '[DisclosedToV]');

Set vNumberFormat = =if(vLocalValue=0, DivisionFormat, Replace(DivisionFormat,'$',''));

[Anil_Babu_Samineni]

I was asking earlier about your result due to this?

the individual variables are working as expected

=num(sum({<Year={$(=max(Year))}>}$(vDisclosedToV)/UnitDivision),'$(vNumberFormat)')

=num(sum({<Year={$(=max(Year))}>} $(vValueField)/UnitDivision),'$(vNumberFormat)')

but while I'm trying to  subtract it is not working(calculating) for conversions

[kvr9]

Hi Anil,

if I use the expression 2 i.e complete if condition instead of variable then the calculation is fine when I select USD and EUR.

If we use the expression 1 i.e variable the the result isi.e the subtraction is taking the value before conversions and appending the $