topic having Variables created in a list box in QlikView

having Variables created in a list box

raghavsurya — Wed, 23 Mar 2011 12:37:36 GMT

Hi All,

Is it possible to have few of the variable created in the qlikview application as a list box. Can anybody help me with this requirement.

Regards,

Raghav

having Variables created in a list box

pover — Wed, 23 Mar 2011 13:04:47 GMT

Not exactly. Imagining what you want to do, I would recommend creating a list of values in the script that is not related to any other table, or a table island, and creating the list box based on those values. If you have the user to be able to change the values, you might also use the inputfield function.

Regards.

having Variables created in a list box

raghavsurya — Wed, 23 Mar 2011 13:19:54 GMT

Hi Karl,

Let me eloborate. I have few straight table charts wherein values are calculated. The resultant values are being stored in variable to arrive at "Average" of all the values or "Min" of all the values or "Max" of all the values.

Now there is a new requirement wherein the user should have a option of getting "Average" of selective resultant values. Also they want a dropdown where they can select .

For Example the tables will something like this.

Particulars	Amount	Multiple	Product
Cash	12000	3	36000

Particulars	Amount	Multiple	Product
EBITDA	8000	6	48000


Particulars	Amount	Multiple	Product
Revenue	10000	5	50000

We have saved the product field of these 3 tables in 3 different variables and one more table is provided to show average or min or max of these 3 values (36000,48000,50000). Now the user wants to have an option to select any two fields to arrive at the Average/Min/Max values.

Can you please suggest how to handle this situation.

Regards,

Raghav

having Variables created in a list box

pover — Wed, 23 Mar 2011 13:39:42 GMT

Is the Particulars a field in the data model that groups accounts or is it just text you add to the straight table? If it is a field you could use a function like avg(aggr(sum(Amount*Multiple),Particulars)) to calculate the average, min or max instead of the variables. The function will respect your selection.

It Particulars is just text when this is going to be a little trickier. What are the variable formulas like and how many particulars do you have? You might be able to do it if you make a inline table of the variable names in your script and then create a list in QlikView that the users selects. The formula to calculate the average, min or max might be

=rangemax($(vList))

where vList is a variable =concat([Field with Variable Names],',')

Regards.

AW:having Variables created in a list box

brenner_martina — Wed, 23 Mar 2011 13:42:18 GMT

Hi,

you can fill the variable dynamic by using concat in the script. Look at my example!

AW:having Variables created in a list box

raghavsurya — Thu, 24 Mar 2011 04:45:40 GMT

Hi Martina,

What we have is multiple variables already created. We would like to have all the variables in one list box.

For examples

Variables are

vRev

vProfit

vCost and so on......

we need all these variables available is a list box.

Thanks and Regards,

Raghav

having Variables created in a list box

raghavsurya — Thu, 24 Mar 2011 05:37:54 GMT

Hi Karl,

Thanks a lot for your kind help. It almost resolved our issue.

Regards,

Raghav

having Variables created in a list box

raghavsurya — Thu, 24 Mar 2011 13:09:07 GMT

Hi Karl,

We are stuck with a situation wherein one of the product is negetive and we would like to eliminate the negative value from the equation to calculate the Min values. The suggestion you had made earlier is perfectly working except for this situation.

For Example

Particulars	Amount	Multiple	Product
Cash	12000	3	36000

Particulars	Amount	Multiple	Product
EBITDA	-2000	6	-12000


Particulars	Amount	Multiple	Product
Revenue	10000	5	50000

When we do Rangemin($(VList) is should not consider the values which are zero or less than zero. Can you please help us to fix this issue.

Regards,

Raghav

having Variables created in a list box

raghavsurya — Fri, 25 Mar 2011 06:07:10 GMT

Hi Karl,

For Example

Particulars	Amount	Multiple	Product
Cash	12000	3	36000

Particulars	Amount	Multiple	Product
EBITDA	-2000	6	-12000


Particulars	Amount	Multiple	Product
Revenue	10000	5	50000

When we do Rangemin($(VList) is should not consider the values which are zero or less than zero. Can you please help us to fix this issue.

Regards,

Raghav

having Variables created in a list box

pover — Fri, 25 Mar 2011 21:35:08 GMT

This is getting a little crazy, but in the script in stead to just putting the list of variable names put the string

if(variableName>0,variableName)

for every variable. You shouldn't have to change anything else and your end result will be

rangemin(if(vEBITDA>0,vEBITDA),if(Cash>0,Cash),if(Revenue>0,Revenue))

Regards.