Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a chart that shows typical sales information: Sales, Margin and GM%. GM% is calculated as Margin / Sales. The Sales and Margin data can be for any year the user selects. They also have a series of If statements in their expression looking at other selections the user can make in the document. What I want to do is make the Sales and Margin column heading dynamic, showing the year selected, but also be able to reference the expression label to make the GM% definition relatively easy. For example, my Sales and Margin labels would look something like this:
=(SelectYear) & ' Sales'
=(SelectYear) & ' Margin'
But how do I reference these expressions in my GM% definition calculation? Before I added the year to the column heading I had:
Sales had the label = Sales
Margin had the label = Margin
And GM% had the definition = [Margin] / [Sales]
I don't want to have to repeat the definitions for Sales and Margin again in the GM% definition if I don't have to because they can be a little involved. Is there a way to have the column label be one value and the actual data another value to make referencing it within the chart easy?
Thanks.
As well as referencing a column name, you can also reference a column number, specifically because of this case.
Your first expression is column 1, then 2, then 3 etc.
So, if you have Sales as your first column, margin as you second, then your expression for GM% should be Column(2)/Column(1)
Hello,
I'm not sure if I got you right but you can do something similar to label
and refer it within an expression='% Sales' & vYear
Now twist this code and change with your own variables. Is that what you are looking for?[% Sales $(vYear)] / 100
As well as referencing a column name, you can also reference a column number, specifically because of this case.
Your first expression is column 1, then 2, then 3 etc.
So, if you have Sales as your first column, margin as you second, then your expression for GM% should be Column(2)/Column(1)
That's it exactly! I figured there had to be a simple way to do it. Thanks.