topic Subtract from a variable in QlikView

Subtract from a variable

zagzebski — Thu, 19 Apr 2012 18:44:14 GMT

New to set analysis...

I my set analysis expression works but I am trying to add one more piece: IncurredMonth={'=$((vMaxMonth)-1)'}

It then takes my results to "0". all I want is to take the variable "vMaxMonth" and subtract one from it but somhow my syntax must be off.

Subtract from a variable

Jason_Michaelides — Thu, 19 Apr 2012 19:07:06 GMT

Try double-quotes instead of the singles.

Re: Subtract from a variable

Thu, 19 Apr 2012 19:17:19 GMT

hello

Since you create the variable (tools / variable overview) can be defined as -1 and no need to put quotes. example:

VarMesAnt = Month (num (if ((month-1) = 0.12, week-1)

, '#, # # 0', '.' , ','))

regards

Subtract from a variable

zagzebski — Mon, 23 Apr 2012 17:33:39 GMT

Nothing seems to have an effect:

If I do just:

sum({<[IncurredYear]={$(vMaxYear)},[IncurredMonth]=,[AMOUNT_TYPE]={'ACTUAL'}}>}[Ending_Balance_CNT])

it returns the same is if I add the extra criteria:

sum({<[IncurredYear]={$(vMaxYear)},[IncurredMonth]=,[AMOUNT_TYPE]={'ACTUAL'},[IncurredMonthNum]={"=vMaxMonth1"}>}[Ending_Balance_CNT])

The expression is pretty much ignoring: [IncurredMonthNum]={"=vMaxMonth1"} so I must be writing it wrong.

vMaxMonth1 is a variable that is = to 3.

Subtract from a variable

Jason_Michaelides — Tue, 24 Apr 2012 08:44:17 GMT

Can you post your app?

Subtract from a variable

spsrk_84 — Tue, 24 Apr 2012 09:23:17 GMT

Hi,

sum({<[IncurredYear]={$(vMaxYear)},[IncurredMonth]=,[AMOUNT_TYPE]={'ACTUAL'},

[IncuredMonth= {$(=vMaxMonth -1)}>}[Ending_Balance_CNT])

try in this way ...

if not try to calculate or get the month-1 value into other variable and pass that variable into Incurred Month.

Regards,

Shiva

Subtract from a variable

masha-ecraft — Tue, 24 Apr 2012 11:34:18 GMT

It should be IncurredMonth={'$(=$(vMaxMonth)-1)'}

$(= ...) instructs Qlikview to evaluate the expression inside the brackets and $(vMaxMonth)-1 is the expression to be evaluated.

Subtract from a variable

zagzebski — Tue, 24 Apr 2012 11:34:41 GMT

Shiva - thanks a ton! That worked.

Steve

Subtract from a variable

zagzebski — Tue, 24 Apr 2012 12:10:19 GMT

Thanks Masha..

Based on what you said:

"It should be IncurredMonth={'$(=$(vMaxMonth)-1)'}

$(= ...) instructs Qlikview to evaluate the expression inside the brackets and $(vMaxMonth)-1 is the expression to be evaluated."

why in my expression which now works

does [IncurredYear]={$(vMaxYear)} not have an "= "before the variable vMaxYear

but

[IncurredMonth= {$(=vMaxMonth -1)} does have an "=" before the variable vMaxMonth -1

Whole expression:

sum({<[IncurredYear]={$(vMaxYear)},[IncurredMonth]=,[AMOUNT_TYPE]={'ACTUAL'},

[IncurredMonth= {$(=vMaxMonth -1)}>}[Ending_Balance_CNT])

Subtract from a variable

masha-ecraft — Tue, 24 Apr 2012 12:17:10 GMT

Equal sign (=) is needed when there is an expression to be evaluated, e.g $(=vMaxMonth -1).

When there is just a variable that is to be replaced with its value, e.g. $(vMaxMonth), no = sign is needed.

The difference between my example and Shiva's is that Shiva is using a variable in the expression, while I'm using the value of the variable.