topic Using a variable to store a mathematical funciton = > < etc. in QlikView

Using a variable to store a mathematical funciton = > < etc.

Wed, 22 Jan 2014 12:23:53 GMT

Hi,

I have the following graph:

By using the following expression:

MAX({<PatientID = PatientID+{'RefA', 'RefB'}>}

IF(PatientID='RefB' AND PositionProcess.Position='Middle', (TimeBetweenEvents)*-1,

IF(ProcessKey<>'0-1',

IF(PositionProcess.Position='Middle' and $(GraphDimension)= $(GraphGroupOne),(TimeBetweenEvents),

If(PositionProcess.Position='Middle' and $(GraphDimension)=$(GraphGroupTwo),(TimeBetweenEvents)*-1,

IF(PositionProcess.Position='Middle',(TimeBetweenEvents),

IF($(GraphDimension)=$(GraphGroupOne),0.0001,-0.0001)))))))

I have currently got $(GraphDimension) set to Gender and $(GraphGroupOne) set to 'Male' and $(GraphGroupTwo) set to 'Female'. This allows me to display the value above and below the axis based on gender. All is good so far....

The reason for the variables is so that the user can change the values within an input box and see different splits. The issue is in most some dimensions like 'Age' I don't just want equals to. I want to say IF....$(GraphDimension) > $(GraphGroupOne)....

So I then created two new variables $(TopSign) and $(BottomSign). If I then use an input box and put equals for both variables and replace the '=' in the script (highlighted in red) I get an error and the graph doesn't display. Similarly if I create a text box and just set the expression to the variable I get a '-' e.g. no valid data. However if I point the value to the other variables it works. If I look in the variable menu it has got the '=' stored.

Does anyone know how I might be able to resolve this issue?

Thanks

Dan

Re: Using a variable to store a mathematical funciton = > < etc.

Wed, 22 Jan 2014 12:44:48 GMT

Hi Dan

How about defining variable as =Chr(61) ?

Lukasz

Re: Using a variable to store a mathematical funciton = > < etc.

Wed, 22 Jan 2014 12:50:53 GMT

That does indeed work but ideally I want the user to be able to put an equals sign or a greater than sign into the box. I don't really want them to have to write the char value. Any way to get around this you can think of?

Re: Using a variable to store a mathematical funciton = > < etc.

Wed, 22 Jan 2014 13:41:11 GMT

Maybe try to create constrained input box with predefined values (symbols enclosed with quotes)

and then use something like this in your expression:

$(=If($(TopSign)='=', Chr(61), $(TopSign)))

Re: Using a variable to store a mathematical funciton = > < etc.

Nicole-Smith — Wed, 22 Jan 2014 13:59:00 GMT

When putting the variable into your calculation, do not put dollar sign expansion around it:

$(GraphDimension) TopSign $(GraphGroupOne)

not

$(GraphDimension) $(TopSign) $(GraphGroupOne)

Re: Using a variable to store a mathematical funciton = > < etc.

Fri, 24 Jan 2014 07:39:36 GMT

Thank you for your suggestions.Lukasz Mastalerz I have created a constrained listbox with values and have tweaked the formula as suggested by Nicole Smith. This works for the following expressoin:

MAX({<PatientID = PatientID+{'RefA', 'RefB'}>}

IF(PatientID='RefB' AND PositionProcess.Position='Middle', (TimeBetweenEvents)*-1,

IF(ProcessKey<>'0-1',

IF(PositionProcess.Position='Middle' and $(GraphDimension) & TopSign & $(GraphGroupOne),(TimeBetweenEvents),

If(PositionProcess.Position='Middle' and $(GraphDimension) = $(GraphGroupTwo),(TimeBetweenEvents)*-1,

IF(PositionProcess.Position='Middle',(TimeBetweenEvents),

IF($(GraphDimension)=$(GraphGroupOne),0.0001,-0.0001)))))))

I however also want to swap out the second function highlighted in Red. I have created a new variable in the same way as TopSign called Bottom Sign. If I try to put the value in there the expression works but no values fall into the '*-1' bucket. I'm not too sure why. Current the TopSign is set to an equals sign. Even if I copy and paste '& TopSign &' to replace the '=' it still fails to group anything with the '*-1' grouping. If however I keep it with a '=' it does.

Thanks for your help to date.

Any ideas anyone?

Re: Using a variable to store a mathematical funciton = > < etc.

Nicole-Smith — Fri, 24 Jan 2014 13:52:42 GMT

I don't think you should have the ampersands around TopSign since you're not actually creating a string (it may show it as underlined in QV, but it isn't actually an error). I think it should just look like this:

$(GraphDimension) TopSign $(GraphGroupOne)

Try changing your expressions to look like that and see if it helps at all.

Re: Using a variable to store a mathematical funciton = > < etc.

Fri, 24 Jan 2014 14:01:10 GMT

Unfortunately withing the ampersands doesn't seeme to work, If I use the following:

MAX({<PatientID = PatientID+{'RefA', 'RefB'}>}

IF(PatientID='RefB' AND PositionProcess.Position='Middle', (TimeBetweenEvents)*-1,

IF(ProcessKey<>'0-1',

IF(PositionProcess.Position='Middle' and $(GraphDimension) TopSign $(GraphGroupOne),(TimeBetweenEvents),

If(PositionProcess.Position='Middle' and $(GraphDimension) = $(GraphGroupTwo),(TimeBetweenEvents)*-1,

IF(PositionProcess.Position='Middle',(TimeBetweenEvents),

IF($(GraphDimension)=$(GraphGroupOne),0.0001,-0.0001)))))))

Nothing is displayed on the graph (the only change I have made is to remove the ampersands).

Thanks

Dan

Re: Using a variable to store a mathematical funciton = > < etc.

Nicole-Smith — Fri, 24 Jan 2014 14:29:20 GMT

I just tested it out in QV, and if you have in your variable TopSign just =, there is no way to return what it actually is.

For example, try putting either TopSign or $(TopSign) in a textbox. It returns null because QV automatically thinks the variable is a calculation because it starts with an equal sign.

I am able to get it to work if TopSign is >, <, >=, or <=, but I don't think it's possible for just =.

I think you'll need to go with Lukasz Mastalerz's suggestion.

EDIT: Another suggestion would be to store the whole Gender = 'Male' in one variable instead of trying to store each separate part.

Re: Re: Using a variable to store a mathematical funciton = > < etc.

jerem1234 — Fri, 24 Jan 2014 14:44:56 GMT

Using a different combinations to what Nicole Smith and Lukasz Mastalerz have done, I think it can work. For your constraint list in your input box, use:

='=';='>';='<'

Therefore, it treats it as a formula already.

Then for your expression, evaluate the variable with $() and without using the ambersand like:

if(avg(Grade) $(vSign) 40, avg(Grade))

This I think should do it.

PFA

Hope this helps!