Fields as variable in expressions

[#1]

Hello,

I have a transactions table that contains fields with keyfigures as gross sales, net sales, margin.

I would like to create a listbox where the analyser just has to select the keyfigure he wants to use in the charts.

The chart expressions would then contain the variable in stead of the field e.g. Sum(varKeyFigure) in stead of Sum(Gross_Sales)

How can I create this in the script?

Any help is appreciated

[#2 · Accepted solution]

You can use variables in your expressions with a dollar sign expansion.

This should work:

Sum($(varKeyFigure))

I would probably just Inline Load all of your Key Figures into a separate (unlinked) table.

LOAD * INLINE [
 KeyFigures
 Gross_Sales
];

And then set your variable value to:

=ONLY(KeyFigures)

That will work as long as one (and only one) selection is made in the key figures box. You can handle multiple selections or no selections as well, but that depends on the needs of the application.

[#3]

Completely agree with NMiller, it is easier to maintain and no need for macro in this case.

Just don't forget to check always one Selected value box for this field.

[#4]

Gentlemen,

This worked fine.

Thanks a lot!

[michael_anthony]

Hi, I'm trying to do something very similiar, but need to include multiple fields, eg. Tonnes and Gross_Sales. I have a Field called KeyFigures with the list of relevant field names and have included that in the Chart as a Dimension.

I can use an Only(KeyFigures) function in the chart expression to return the field name, but can't get it to recognise that with in the Sum, ie. Sum(Only(KeyFigures)). I have tried various $ sign formats without success. It does work where only one KeyFigure is selected, but not multiple.

You suggested above that "can handle multiple selections or no selections as well", but not sure if I need to do something else to make it work.

Thanks.