Using a variable in a button

[#1]

I am currently using a variable to divide one field by another. I am trying to create a button that can choose if that variable is <85% or >115% using a search string.  Is there anyway to make a button using only that variable?

[Gysbert_Wassenaar]

Can you explain in more detail what you're trying to do? A small qlikview document with some data would be helpful.

[#3]

I have a variable, vBurnRate, that is dividing two fields from different tables. The outcome of vBurnRate is a percent. In order to place a metric on the data, anything <85% is red. 86-89% is yellow. 90-110% is green.  111-114% is yellow. And >115% is red. I distinguish these metrics using background colors. All I need the button to do is, for example, show the ones that are <85% which happen to be red. If this was a normal field, I could use a button with a select in field action using the field name and I could use the search string ("<85%") to get my answer. If this doesn't clarify it, I can try to make a sample qlikview document.

[johnw]

Let me see if I maybe understand:

1. vBurnRate = FieldA / FieldB
2. FieldA is on TableA
3. FieldB is on TableB
4. There's a chart with some dimensions and an expression of $(vBurnRate)
5. Button should select the rows of the chart where the $(vBurnRate) < 85% or > 115%

I'm guessing there's some key linking TableA and TableB.

Then maybe the button would select values from this key field with a search expression like "=$(vBurnRate)<.85 or $(vBurnRate)>1.15".

If I don't understand, or I do understand but that doesn't work, then yeah, a sample document to clarify and play with would be helpful.

[susovan]

Hi,

Is it your requirement?

[#6]

Yes you are on the right track.

1. vBurnRate = $(vDevBurn)/[Developer.Dev % Complete]

    vDevBurn = $(vActualEffort)/[Developer.Estimated Effort]

    vActualEffort = (sum({$<[Tracker.Category]={"Design", "Development", "Testing"}>} [Tracker.Time Reported Actual]))

2. Tracker.Category and Tracker.Time Reported Actual come from a table called Tracker.

3. Developer. Dev % Complete and Developer.Dev Estimated Effort come from a table called Developer.

     These tables are both linked together by the field ID Number.    

4. There's a straight table with dimensions Dev.% Complete, Developer.Number and Developer.Developer. There are two expressions: $(vBurnRate) and $(vDevBurn) which is $(vActualEffort)/([Dev.Estimated Efforts])

5. Yes, the button should select rows of the chart when $(vBurnRate) <85% or >115%. I have these distinguished with background colors. <85% and >115% is red. 86-89% and 111-114% is yellow (>85% but <90%, and >110%, but <115%).90-110% is green. (>=90%, but <=110%)

I have attached a sample QV file that shows my chart. I have tried using that search expression you gave me but it did not work. Thanks for your help so far!

[#7]

I'm not sure what you mean. I have tried using Set a Variable button before and it has not worked.

[johnw · Accepted solution]

I was trying to say that you need to do the select in the key field linking the tables, so in [ID Number], not in the variable, but I guess that wasn't very clear. I also incorrectly gave you the search expression you would type in the list box, but that's an incomplete expression for the select in field search string. Do this:

Select in Field
[ID Number]
='=$(vBurnRate)<.85 or $(vBurnRate)>1.15'

It does what I think you're asking for. See attached.

[#9]

Thank you so much that worked great! I got it to work for the red and green buttons.

Now I am trying to figure out the yellow button. I got one metric to work with it, but I cannot get the other. I got ='=$(vBurnRate)>=.85 and $(vBurnRate)<0.9' to work, but I also need to include ='=$(vBurnRate)>1.1 and $(vBurnRate)<=1.15' somehow. I tried putting or between the two but I kept getting an error.

Thanks again for all your help!

[johnw]

='=($(vBurnRate)>=.85 and $(vBurnRate)<.9) or ($(vBurnRate)>1.1 and $(vBurnRate)<1.15)'