having Variables created in a list box

[raghavsurya]

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

[pover]

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.

[raghavsurya]

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

[pover]

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.

[brenner_martina]

Hi,

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

[raghavsurya]

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

[raghavsurya · Accepted solution]

Hi Karl,

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

Regards,

Raghav

[raghavsurya]

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

[raghavsurya]

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

[pover]

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.