Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using a variable in a button

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?

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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.

View solution in original post

13 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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


talk is cheap, supply exceeds demand
Not applicable
Author

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
Champion III
Champion III

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
Partner - Specialist
Partner - Specialist

Hi,

Is it your requirement?

1.JPG

2.JPG

3.JPG

Warm Regards,
Susovan
Not applicable
Author

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!


Not applicable
Author

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

johnw
Champion III
Champion III

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.

Not applicable
Author

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
Champion III
Champion III

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