topic Variable to make a dynamic expression in QlikView

Variable to make a dynamic expression

Mon, 03 Jan 2011 11:43:59 GMT

Hi,

I want to add a variable to my expressions to make it dynamic in the sense of what is inside the sum() and avg() functions.

I have two charts right now using the expression sum(Profit) and avg(Profit). But instead of having Profit as a fixed term I want to add a variable that is controlled by a combo box next to the charts to use Sales and Quantity instead (ie. sum(Sales) if Sales is selected, sum(Quantity) if quantity is selected etc.).

How do I add this variable and how do I create the combo box that controls it?

Variable to make a dynamic expression

tresB — Mon, 03 Jan 2011 12:09:42 GMT

Hi,

May be this would not be the best way to achieve what you want. but right now what i have in mind to achieve it is as follows: make two similar charts with two different expressions (Sum(Sales) and Sum(Quantity)). Create an inline load of a field say "SELECT" with values 'Sales' and 'Quantity'. now put the expression in "Layout tab->Show->Conditional" accordingly (SELECT="Sales"/"Quantity"). now put the identical(but with different expression) charts overlapped. now the charts would be shown according to your selection.

Hope this would help you.

regards, tresesco

Variable to make a dynamic expression

lironbaram — Mon, 03 Jan 2011 12:28:00 GMT

hei attach is an example

hope it helps you

you can as much expressions as you want

this way

Variable to make a dynamic expression

Mon, 03 Jan 2011 12:51:12 GMT

Thank you RoiUser!

That's exactly what I'm after.

What I do not understand in your code though is your references to "$(SumGraph)" and "$(AvgGraph)" in expression definitions.

Where are these variables set? I Can't find SumGraph anywhere else.

Variable to make a dynamic expression

lironbaram — Mon, 03 Jan 2011 13:46:59 GMT

hei

look at the varaibles of the document

yuo'll see those vars

they get the string of the function, by calling the var with $() it calculating the string as function

Variable to make a dynamic expression

Mon, 03 Jan 2011 14:14:30 GMT

Thank you,

I found it now. I had never used the variable window before so I didn't know where it was.

I also added the label "='Total ' & field" to the graph to make the label interactive.

Variable to make a dynamic expression

Mon, 03 Jan 2011 16:26:42 GMT

By the way, is there a way to make the variable automatically choose the first value of the table?

So if the table contains Sales and Quantity it auto selects Sales. But if the table only contains Quantity (ie Quantity is selected, ergo is the only value in the table) then the variable of course chooses quantity.

This is to eliminate the situation where nothing is selected which renders the graphs useless (since no value is selected which makes the variable unset).

I have an idea to do this using =max(sumformula) for the variable declaration instead of =sumformula. But I don't know the proper syntax for it..

Re: Variable to make a dynamic expression

shahamel — Fri, 17 Aug 2012 09:53:25 GMT

I look at your file It is really great. I really like your variables in this application. They way they react to a fieldselection is precisly what i need. I just can't figure out how you made those variables. I tried to make them myself, but they do not respond to my field selection.

According to your file you have a variable:

AvgGraph

When i look at the variable overview i see

AvgGraph = avgformula

So i thought. If i make my own field, for example Storename and i make a variable named Store.

SET Store = Storename;

I tried to test it with an inputfield. the variable shoul change with it when i change my fieldvalue in the field Storename.

For example ,.when i select winkel1 in Storename, my variable should automaticly change into winkel1 as wel, but stays 'Storename'.

Would you be so kind to tell me how you made your SET variabel in your script?

Thanks