If you wanted to change just a specific parameters, you could do that with a variable. For example, you could set up a fileglobal variable named chartField. By pressing different buttons (or perhaps using a popup menu) you could set this variable to “Revenue” or “Profit”, which I assume are different fields in your database. Then you could set your chart formula to:
chart(
summarytable(
“database”, “Volume”,
“groups”, {groupdatepattern(Date,“YYYY-Mon”)},
“total”, chartField,
"format", “plainnototals”),
“chart”, “line”,
“titles”, chartField)
When you press the buttons, the chart display will change. You could also use this technique to dynamically change a chart to switch between bar/line/area charts, for example. Or you could put the date pattern into variable and switch between years, quarters or months. Or you could do all of these things at once.
However, you are asking for something a bit more ambitious, you want to change the structure of the chart. In one case you want a single field, in the second case you want two fields. You can’t do this by simply changing a variable, this actually takes a different number of parameters to the summarytable( function, which means it can’t be done in a single function. You’ll need a different function depending on how many fields are being displayed.
There are multiple ways this problem could be tackled, I’m going to suggest just one. Start by setting up a chartFields variable, which as you press different buttons will be filled with a carriage return separated list of field names, for example
letfileglobal chartFields = commatocr("Revenue,Profit")
Now we’ll use the switch( function to build a single chart formula that will adjust depending on how many fields have been specified.
chart(
switch(linecount(chartFields),
1,summarytable(
“database”, “Volume”,
“groups”, {groupdatepattern(Date,“YYYY-Mon”)},
“total”, nthline(chartFields,1),
"format", “plainnototals”),
2,summarytable(
“database”, “Volume”,
“groups”, {groupdatepattern(Date,“YYYY-Mon”)},
“total”, nthline(chartFields,1),
“total”, nthline(chartFields,2),
"format", “plainnototals”),
error("Too many fields!")
),
“chart”, “line”,
“titles”, chartField)
)
Note: I composed this formula here in this forum web page, so it might have syntax errors. But it should get you pointed in the right direction.
In this example I only set up the formula for one or two chart fields, but you could extend it to 3 or more if you wanted. This example will only chart field totals, but you could make it further customizable (to display averages, etc.) by making chartFields a two dimensional array.
As I mentioned above, there are other possibly ways to do this. One approach would be to calculate the output of summarytable( in advance, when the button is pressed. Another option would be to calculate the formula on the fly, and then use the formulavalue( function to calculate the actual chart data. This approach gives you the ultimate in flexibility, and is the way the Summary Workshop works. But it’s probably also the most complicated.
P.S. The fact that you tried to plop a single variable into a formula where multiple parameters are required tells me that you might need to go back and review basic formula syntax and operation. Formula grammar isn’t really complicated, but you’ll need to understand it forwards and backwards if you want to tackle complicated operations.
In your formula you tried to replace two parameters
a,b
with one
c
but these aren’t equivalent, you can’t encapsulate two or more parameters into a single value. (There are techniques for encapsulating multiple values into a single value, for example arrays and dictionaries, but these can only be used where they are specifically allowed, and the summarytable( function doesn’t have any support for this. If it did, your solution would be simpler, but making a basic chart would be significantly more complicated.)