how to get value on specific column on the chart?

[#1]

Hi Colleagues,

I'm working to get one specific value (dynamic) from a chart (pivot tables or bar chart).

Basically I want to create a index line in the chart, but as the first value (Product in this case) can be different, I want a calculation always considering the first column, line 4 (final price).

Product	a	b	c
cost 1	5	2	3
cost 2	4	5	3
final cost	9	7	6
Index	100	78	67

I would like to know if anybody has a good solution for it.

Thanks in advance,

Leandro Duarte

[swuehl]

You could probably try using chart inter record functions, please have a look into the help for full details.

I would try a combination of first() and above().

If you could post a small sample file here (via upload in advanced editor), I can try helping you with the syntax.

Regards,

Stefan

[#3]

Hi Stefan, please find enclosed the example data.

Basically, I need a index (comparison first column with the other column) for the total cost row only.

Please note, it can be dynamic, can be different the first column/period according to selection.

Thanks in advance,

Leandro Duarte

[SunilChauhan]

use

(sum(Cost)/first(column(1)))*100 in Index expression

[swuehl]

I would suggest this:

=(sum(Cost)/first(sum(Cost)))*100

See attached.

I assumed you want Product sorted by Total Cost desc?

Regards,

Stefan

[#6]

Hi Stefan and Sunil, it is working in this case, but if included more than one common field for the product for instance, the value is not appearing.

Please find enclosed the example.... any suggestion?

Thanks a lot

Leandro

[swuehl · Accepted solution]

Try a

=(sum(Cost)/first(total sum(Cost)))*100

in this case.

Regards,

Stefan

[llauses243]

Hi Leandro,

This is my offer, pls to look .rar attached

Good luck, Luis