Variable to make a dynamic expression

[#1]

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?

[tresesco]

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

[lironbaram · Accepted solution]

hei attach is an example

hope it helps you

you can as much expressions as you want

this way

[#4]

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.

[lironbaram]

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

[#6]

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.

[#7]

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..

[shahamel]

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