topic Re: Using a variable in a button in QlikView

Using a variable in a button

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Using a variable in a button

Gysbert_Wassenaar — Tue, 05 Jul 2016 15:02:30 GMT

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

Re: Using a variable in a button

Tue, 05 Jul 2016 15:58:43 GMT

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.

Re: Using a variable in a button

johnw — Tue, 05 Jul 2016 23:41:04 GMT

Let me see if I maybe understand:

vBurnRate = FieldA / FieldB
FieldA is on TableA
FieldB is on TableB
There's a chart with some dimensions and an expression of $(vBurnRate)
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.

Re: Using a variable in a button

susovan — Wed, 06 Jul 2016 04:33:02 GMT

Hi,

Is it your requirement?

Re: Using a variable in a button

Wed, 06 Jul 2016 13:59:12 GMT

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!

Re: Using a variable in a button

Wed, 06 Jul 2016 14:00:11 GMT

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

Re: Using a variable in a button

johnw — Wed, 06 Jul 2016 16:12:51 GMT

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.

Re: Using a variable in a button

Wed, 06 Jul 2016 16:44:48 GMT

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!

Re: Using a variable in a button

johnw — Wed, 06 Jul 2016 17:48:09 GMT

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

Re: Using a variable in a button

Wed, 06 Jul 2016 18:25:00 GMT

Its working in my sample qv document, but not in my actual one for some reason. It keeps putting the yellows along with the red selections.

Re: Using a variable in a button

johnw — Wed, 06 Jul 2016 21:30:22 GMT

Ah, right. For all the buttons, add another action before the select in field that clears the field first. If you already have selections, things can get weird. I didn't think about that. I'm not certain that's the problem here, but it's a problem that you'll want to fix. If we're lucky, that also fixes the bad behavior you're seeing.

Re: Using a variable in a button

Thu, 07 Jul 2016 12:21:45 GMT

It still is not working after putting the Clear field actions before the select in field actions. My yellow is actually being selected when the red button is selected. This is what it looks like below:

I'm not sure why it would do that with our credentials we set.

Red: ='=$(vBurnRate)<0.85 or $(vBurnRate)>1.15'

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

Green: ='=$(vBurnRate)>=0.90 and $(vBurnRate)<=1.10'

Re: Using a variable in a button

johnw — Thu, 07 Jul 2016 16:48:13 GMT

I'm not spotting anything wrong there. It might have something to do with how the real data model looks vs. the simplified example one, or any number of other things that might be different in the real application. It's possible to scramble and reduce the data for upload if you want to try sharing the real application. I'm not sure what else to suggest at this point.