topic Error in a variable formula in QlikView

Error in a variable formula

Fri, 23 Apr 2010 16:49:57 GMT

Hi everyone, I have this problem. I have a simple table with 4 expressions (columns). Two of them are simple sums("Cost" ; "Income"), the third one ("Profit") is a variable (vProfit) which takes the value from a specific cell of another simple table using this macro:

Sub vProfit

Dim v ,c,d, f,h

set IB=ActiveDocument.GetSheetObject("CH08")

v=IB.GetCell(1,7).text

set c = ActiveDocument.Variables("vProfit")

c.SetContent v,true

Activedocument.GetApplication.Refresh

End Sub

The four one should be the result of this formula Incomes + vProfit - Cost. but I can´t make it work.

The first three values change everytime I select a different year or month in two List Box, but the four expression (Result) always shows a "-", a null value.

I've tried to make the expression "Result" with this formula

 Income+vProfit-Cost

and with this one

 Income+Profit-Cost

but the cell "Result" remains the same, blank.

Anyone has an idea.

Thanks in advance, an good weekend.

Error in a variable formula

Fri, 23 Apr 2010 18:58:57 GMT

Hi there, what I suggest is creating a text object and just put a expression like:

=vProfit

to see if your macro is working, if it is, probably you have null values in Income of cost, you can try:

rangesum(Income, Profit, -Cost)

Error in a variable formula

Mon, 26 Apr 2010 09:26:02 GMT

Hi Ivan, thanks for you reply. The macro works but I can't get teh final result. I've tried several options


rangesum(Income, Profit,-Cost)
rangesum(Income, -Cost) + Profit
Using the variable VProfit instead of the expression Profit
rangesum(Income, vProfit,-Cost)
rangesum(Income, -Cost) + vProfit

I think the problem lies on the variable vProit itself. Because I'tried the following formula to check if tthe variable is correct and the expression Result gives a null value.


=vProfit * 2

When It takes the value Profit using the macro, the macro does the following code


set IB=ActiveDocument.GetSheetObject("CH08")
v=IB.GetCell(1,7).text

Maybe the problem is the GETCELL.text, because it takes the value as a text. What you think?