topic Re: Subtraction of two SET Variables in QlikView

Subtraction of two SET Variables

kvr9 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Subtraction of two SET Variables

sunny_talwar — Thu, 16 Nov 2017 12:33:39 GMT

May be try with parenthesis

(vValue) - (vSale)

Re: Subtraction of two SET Variables

kvr9 — Thu, 16 Nov 2017 12:38:30 GMT

Hi Sunny,

Sorry missed to mention.I used

sum(vValue) -sum(vSale)

One more point the code is working when I use the expression

sum(if(vCountry =1,Localcurrency, Localcurrency*exchangerate)) - sum(

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

)

instead of variable that where I'm confused should I use any function before this variables to make the subtraction to work??

Re: Subtraction of two SET Variables

sunny_talwar — Thu, 16 Nov 2017 12:40:54 GMT

Are you using an equal sign when you store this in the variable? May be remove the equal sign from the expression and then check

Re: Subtraction of two SET Variables

sumanta12 — Thu, 16 Nov 2017 12:42:41 GMT

Hi,

Instead of SET use LET to store value.

Re: Subtraction of two SET Variables

kvr9 — Thu, 16 Nov 2017 12:49:28 GMT

Hi Sunny,

I defined as mentioned above,can you please tell me if any thing wrong in above statement

Re: Subtraction of two SET Variables

HirisH_V7 — Thu, 16 Nov 2017 12:50:00 GMT

Hi,

Try this

$(vValue) - $(vSale)

in text box,

where as in expression,

Sum($(vValue) )- Sum($(vSale))

Re: Subtraction of two SET Variables

kvr9 — Thu, 16 Nov 2017 12:50:57 GMT

Hi Sumanth,

Since I used set to store the variable I prefixed it with $ while using in expression.

Re: Subtraction of two SET Variables

Peter_Cammaert — Thu, 16 Nov 2017 12:56:48 GMT

The variables seem ok, but AFAIK the way you try to use their content is wrong. You don't want to use a LET statement or preliminary variable evaluation by prefixing the string in each with an equal sign. Both lead to either errors or nothing.

You want simple text substitution so that

Sum(Variable)

leads to

Sum(ExpressionInsideVariable)

before the Sum() is calculated. Please try with

=Sum($(vValue)) - Sum($(vSales))

Re: Subtraction of two SET Variables

kvr9 — Thu, 16 Nov 2017 13:13:45 GMT

Hi Peter,

Please find the my expression below.

Re: Subtraction of two SET Variables

Peter_Cammaert — Thu, 16 Nov 2017 13:44:30 GMT

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...

Re: Subtraction of two SET Variables

Peter_Cammaert — Thu, 16 Nov 2017 13:46:52 GMT

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?

Re: Subtraction of two SET Variables

kvr9 — Fri, 17 Nov 2017 06:35:28 GMT

Hi Peter,

Please find the set variables statements.

Re: Subtraction of two SET Variables

Peter_Cammaert — Fri, 17 Nov 2017 08:37:02 GMT

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.

Re: Subtraction of two SET Variables

kvr9 — Mon, 20 Nov 2017 06:25:02 GMT

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.

Re: Subtraction of two SET Variables

kvr9 — Mon, 20 Nov 2017 06:28:04 GMT

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

Re: Subtraction of two SET Variables

Anil_Babu_Samineni — Mon, 20 Nov 2017 06:31:31 GMT

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

Re: Subtraction of two SET Variables

kvr9 — Mon, 20 Nov 2017 06:38:32 GMT

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

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,'$',''));

Re: Subtraction of two SET Variables

Anil_Babu_Samineni — Mon, 20 Nov 2017 07:11:18 GMT

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

Re: Subtraction of two SET Variables

kvr9 — Mon, 20 Nov 2017 09:49:20 GMT

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 $