topic Re: Variable not displaying the value in QlikView

Variable not displaying the value

Anonymous — Fri, 20 Nov 2020 18:02:30 GMT

Hello,

I created a variable called vTotal which is a formula and it contains one of the expressions I pulled out of the chart. I just

wanted to save this value and use this variable when I need it. So this is what I did but I can't get the value to display in a text object.

1. Settings -> Variable Overview -> Add

I named it "vTotal" and pasted the expression into the Definition box.

2. Placed a text object and set the Text to "=$(vTotal)".

3. Saved the QVW file

4. Reloaded it.

The text object shows "--". Looks like two dashes and I'm not able to see the result of the expression.

Am I doing this correctly or am I missing something?

Thanks

Re: Variable not displaying the value

sunny_talwar — Fri, 26 Feb 2016 19:26:08 GMT

Try some variations:

=$(=vTotal) or

=$(vTotal)

you can also play around with expression stored within vTotal. Try it with and without '=' sign. =Expression vs. Expression.

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 19:31:17 GMT

The formula uses AGGR(SUM()) just as an additional info.

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 19:37:01 GMT

I tried:

=$(=vTotal) or

=$(vTotal)

Nothing is showing.

Here is the actual expression set to the variable vTotal:

AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4"} >}[Amount1]) ,%Col1,%KEY_FY)

AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4)"} >}[Amount2]) ,%Col1,%KEY_FY)

Re: Variable not displaying the value

Clever_Anjos — Fri, 26 Feb 2016 19:44:27 GMT

Try

"=$(=vTotal)"

If does not work, please post your expression

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 19:47:17 GMT

I just tried it. The text shows "=$(=vTotal)".

See my expression in an earlier reply.

Re: Variable not displaying the value

Digvijay_Singh — Fri, 26 Feb 2016 19:50:08 GMT

I think one bracket is missing as shown in red below -

AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4)"} >}[Amount1]) ,%Col1,%KEY_FY)

AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4)"} >}[Amount2]) ,%Col1,%KEY_FY)

Re: Variable not displaying the value

Clever_Anjos — Fri, 26 Feb 2016 19:54:17 GMT

Set vTotal as (please note the "="

= AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4"} >}[Amount1]) ,%Col1,%KEY_FY)

AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4)"} >}[Amount2]) ,%Col1,%KEY_FY)

Use $(vTotal)

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 19:55:04 GMT

No, I didn't miss the bracket. It is correct in QVW file. It was just missing in this post. Thanks for catching it.

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 19:59:15 GMT

I actually did test it with prefixing with the "=" sign in the definition box where I add this variable. But I just did it again

and in my text box's text property, I have it as $(vTotal). I saved it, reloaded the qvw file. The text box now shows

$(vTotal). What's going on here???

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 20:02:39 GMT

I deleted this variable. I now placed this code in the load script as:

SET vTotal =

= AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4)"} >}[Amount1]) ,%Col1,%KEY_FY)

AGGR(SUM({1 <%Col1 = {"$(=Col2)"}, %Col3 = {"$(=Col4)"} >}[Amount2]) ,%Col1,%KEY_FY)

I'm getting the two dashes in my text box.

Re: Variable not displaying the value

Anonymous — Fri, 26 Feb 2016 21:34:55 GMT

It seems like Qlikview doesn't like AGGR(). When I removed the AGGR() function, the text box showed a value. Can some validate my claim??

Re: Variable not displaying the value

sunny_talwar — Fri, 26 Feb 2016 21:50:17 GMT

In a text box, the above variable will only show a value if the Aggregated table has all rows with the same information. If now, Then I believe you need to add a Sum(Aggr()) or Count(Aggr()) or some kind of function so that it can consolidate all the values and spit a single number in a text box object.

Re: Variable not displaying the value

engishfaque — Mon, 29 Feb 2016 04:11:18 GMT

Dear Sidneylu,

Please keep in mind, when you are creating variable then you must select Variable after that write your expression in your Variable Overview => Definition Box.

When you are calling your variable name then use Dollar Sign Expenssion ($).

Kind regards,

Ishfaque Ahmed

Re: Variable not displaying the value

Anonymous — Mon, 29 Feb 2016 13:43:00 GMT

All the data is there. The chart which contains that expression is being displayed properly. So I just took that expression and made it a formula so I can use it when I need it. One thing I'm not sure is when the formula gets evaluated? Is it possible is a timing issue in my case? Again I have different tabs in my QVW file. So does the formula get evaluated after the tab is clicked and all the selections have been made or as sson as the tab is clicked, it runs the formula first?

Re: Variable not displaying the value

jonathandienst — Mon, 29 Feb 2016 13:53:18 GMT

The problem is that the $ expansion is performed before the rest of the expression is evaluated. This means the $ expansion also applies to Set statements, which you might not expect. The $ expansions do not work so they expand to null.

You need to defer the $ expansions. One way that I prefer is:

Set vTotal = = AGGR(SUM({1 <%Col1 = {"#(=Col2)"}, %Col3 = {"#(=Col4)"} >}[Amount1]) ,%Col1,%KEY_FY)

AGGR(SUM({1 <%Col1 = {"#(=Col2)"}, %Col3 = {"#(=Col4)"} >}[Amount2]) ,%Col1,%KEY_FY);

Let vTotal = Replace(vTotal, '#(', '$(');

Re: Variable not displaying the value

Anonymous — Mon, 29 Feb 2016 15:26:19 GMT

I'm not able to get this to work. First I placed the SET & LET you provided in my edit script and before any LOAD commands. When I hit CTRL R, I got an error on the LET command. Then I move the LET to the end and after all the

LOAD statements completed. I was able to refresh without any issue. However the text box which is set to

=$(vTotal) still show blank. Am I not doing this correctly?

Re: Variable not displaying the value

jonathandienst — Tue, 01 Mar 2016 08:46:29 GMT

Change the Let to:

Let vTotal = Replace(vTotal, '#', '$');

(Even here, QV was attempting a $ expansion!)

Re: Variable not displaying the value

Anonymous — Tue, 01 Mar 2016 14:34:28 GMT

OK. I revised the LET and placed both the SET and LET statements before any LOAD statement. I was able to refresh it. However I get this error now. Any idea what this could be? Thanks.

Re: Variable not displaying the value

Anonymous — Tue, 01 Mar 2016 14:38:35 GMT

Just wanted to clarify my earlier reply. I was playing around with the SET statement yesterday and had removed the AGGR(). So when I ran this morning, I got the above "garbage after expression" error. When I put the AGG() back, the text box is still blank (or showing two tiny dashes).