Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
chrisbrown1
Contributor III
Contributor III

Edit Expression Error with Variable and if statement

I have been using a variable to scale my numbers to Billions, Millions and Thousands etc.

This works fine except when I introduce an if statement.

For instance this works great (Number formatting - Measure Expression):

$(vScaleNumber(sum([Req Estimated Total Value]))) --> $2.8B or $1.7M etc...

I usually just take my number and wrap it in a variable $(vScaleNumber(sum(XXX))) but when I introduce an If statement I get an error in the expression editor.

=$(vScaleNumber(sum(if(isnull([Calc - Approval Days]), 0, [Req Estimated Total Value]))))

Error in expression:')' or';' expected

Any ideas would be appreciated.

Labels (1)
1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

To make it work I had to load the variables from an external file.

As an example I used an xlsx file but you could have a table in some database with all your variables.

SET NullInterpret = 'NULL';

Test:
NOCONCATENATE
LOAD *
INLINE [
        ReqID|"Calc - Req Post RAD Approval Days"|"Req Estimated Total Value"
        RQMT-00017|3|100000034
        RQMT-00015|NULL|3456789
        RQMT-00011|56|100000
        RQMT-00010|12|1000000
        RQMT-00009|NULL|567000
        RQMT-00005|2|1000
] (delimiter is '|');

Variables:
NOCONCATENATE
LOAD
	variable_name,
	Replace(variable_exp, '$(', '?(') AS variable_exp 
 FROM [lib://Downloads/master_variables.xlsx]
(ooxml, embedded labels, table is Variables);


LET i_to = NoOfRows('Variables') - 1;

FOR i = 0 TO i_to

  LET varname = Peek('variable_name', $(i), 'Variables');
  LET varexpr = Peek('variable_exp',  $(i), 'Variables');
  
  LET $(varname) = Replace('$(varexpr)', Chr(63) & Chr(40), Chr(36) & Chr(40));

NEXT

LET i_to 	=;
LET i 	 	=;
LET varname =;
LET varexpr =;

DROP TABLE Variables;

View solution in original post

8 Replies
lorenzoconforti
Specialist II
Specialist II

How is vScaleNumber defined?

JGMDataAnalysis
Creator III
Creator III

The issue is generated by the commas of the expression you are passing as argument to your variable.

The vScaleNumber variable expects a single argument ($1) but "understands" that you are passing three to it.

CommunityQlik_VariableParameter.PNG

You could solve the problem applying the technique explained in the following link:

https://community.qlik.com/t5/QlikView-Documents/Comma-problem-workaround-for-dollar-sign-expansion-...

 
chrisbrown1
Contributor III
Contributor III
Author

JGMDataAnalysis thanks for the insight. I now know what the issue is but how is the syntax structured? 

// Definition for Scaling numbers
SET vScaleNumber=if($1>=1000000000, dual(num($1/1000000000,'$#,##0.0B'),$1),if($1>=1000000, dual(num($1/1000000,'$#,##0.0M'),$1),if($1>=1000, dual(num($1/1000,'$#,##0.0K'),$1),
if($1<=-1000000000, dual(num($1/1000000000,'$#,##0.0B'),$1),if($1<=-1000000, dual(num($1/1000000,'$#,##0.0M'),$1),if($1<=-1000, dual(num($1/1000,'$#,##0.0K'),$1),num($1,'$#,##0.0')))))));

Usage as KPI measure: Error

$(vScaleNumber(sum(if(IsNull([Calc - Req Post RAD Approval Days]), 0, [Req Estimated Total Value]))))

Using Replace workaround SUM({$< $(=REPLACE('$1', ';', ',')) >} quantity) : ????

lorenzoconforti
Specialist II
Specialist II

Can you do it the other way round?

=if(isnull([Calc - Approval Days]), vScaleNumber(0),vScaleNumber(sum( [Req Estimated Total Value]))

 

JGMDataAnalysis
Creator III
Creator III

For your particular case you could use the solution proposed by @agolorenzoconforti or apply the following ...

QlikCommunity_Variables.PNG

QlikCommunity_vReplaceSemicolon.PNG

QlikCommunity_vScaleNumber.PNG

 

Variable Call:

$(vScaleNumber(Sum(If(IsNull([Calc - Req Post RAD Approval Days]); 0; [Req Estimated Total Value]))))

In the variable call take the precaution of replacing the commas with semicolons!!!

$(vScaleNumber(Your expression with semicolons instead of commas))

 

chrisbrown1
Contributor III
Contributor III
Author

@JGMDataAnalysis, I still can't get this to work.

Create 2 variables:

SET vReplaceSemicolon=$(=Replace('$1', ';', ','));

SET vScaleNumberNew=If(fAbs($(vReplaceSemicolon($1))) >= Pow(10,9), Dual(Num($(vReplaceSemicolon($1)) / Pow(10, 9), '$#,##0.0B'), $(vReplaceSemicolon($1))),
If(fAbs($(vReplaceSemicolon($1))) >= Pow(10,6), Dual(Num($(vReplaceSemicolon($1)) / Pow(10, 6), '$#,##0.0M'), $(vReplaceSemicolon($1))),
If(fAbs($(vReplaceSemicolon($1))) >= Pow(10,3), Dual(Num($(vReplaceSemicolon($1)) / Pow(10, 3), '$#,##0.0K'), $(vReplaceSemicolon($1))),
Dual(Num($(vReplaceSemicolon($1)), '$#,##0.0'), $(vReplaceSemicolon($1))))));

// When you inspect the vScaleNumberNew there is still an error 

chrisbrown1_0-1587401031090.png

//Create Test Data:

RAD:

LOAD * inline [
ReqID|"Calc - Req Post RAD Approval Days"|"Req Estimated Total Value"
RQMT-00017|3|100000034
RQMT-00015| |3456789
RQMT-00011|56|100000
RQMT-00010|12|1000000
RQMT-00009| |567000
RQMT-00005|2|1000
] (delimiter is '|');

//Create KPIs: 

$(vScaleNumberNew(Sum(If(IsNull([Calc - Req Post RAD Approval Days]); 0; [Req Estimated Total Value]))))

Test KPI vScaleNumberNew.PNG

JGMDataAnalysis
Creator III
Creator III

To make it work I had to load the variables from an external file.

As an example I used an xlsx file but you could have a table in some database with all your variables.

SET NullInterpret = 'NULL';

Test:
NOCONCATENATE
LOAD *
INLINE [
        ReqID|"Calc - Req Post RAD Approval Days"|"Req Estimated Total Value"
        RQMT-00017|3|100000034
        RQMT-00015|NULL|3456789
        RQMT-00011|56|100000
        RQMT-00010|12|1000000
        RQMT-00009|NULL|567000
        RQMT-00005|2|1000
] (delimiter is '|');

Variables:
NOCONCATENATE
LOAD
	variable_name,
	Replace(variable_exp, '$(', '?(') AS variable_exp 
 FROM [lib://Downloads/master_variables.xlsx]
(ooxml, embedded labels, table is Variables);


LET i_to = NoOfRows('Variables') - 1;

FOR i = 0 TO i_to

  LET varname = Peek('variable_name', $(i), 'Variables');
  LET varexpr = Peek('variable_exp',  $(i), 'Variables');
  
  LET $(varname) = Replace('$(varexpr)', Chr(63) & Chr(40), Chr(36) & Chr(40));

NEXT

LET i_to 	=;
LET i 	 	=;
LET varname =;
LET varexpr =;

DROP TABLE Variables;
chrisbrown1
Contributor III
Contributor III
Author

Thank you for your assistance JGM.