Using first Selected MonthYear to compare values of succeeding MonthYears selected

[#1]

Hi All,

I'm trying to create a line chart of cost changes of an item using the first MonthYear in the selection to each succeeding MonthYear selected.

For example if my selections are Jan-2010 through Dec-2010 and my first cost is $1.00 and my cost in Feb-2010 is $1.25 then the cost change for the first plotted change would be $0.25 likewise if Mar-2010 is $0.90 my second plotted change is $0.10- .

Any thoughts?

Thanks in advance,

Rich

[tanelry]

I think in chart expression you need to subtract first month value from every single month value.
So the result is lines, all starting at 0 and plotting fluctuation around 0.

Try something like this:

sum(Cost) -
sum(total <Item> if(Month=$(=min(total Month)), Cost))

First line is monthly value, second line is constant of first month's value per each Item.

[#3]

Thanks for your reply however

I couldn't get your expression to work so I started trying other approaches. In an attempt to get my values straight, I've created a pivot table with ITEM and MonthYear as dimensions. The first expression is Sum([Trans Amount]) / Sum ([Trans Quantity]) to give me the Item MonthYear average cost. The second expression is

(sum({$<Year = {$(=min(Year))}, Month = {"<=$(=min({<Year={$(=min(Year))}>} Month))>$(=min({<Year={$(=min(Year))}>} Month)-1)"}>} [Trans Amount])

/

(sum({$<Year = {$(=min(Year))}, Month = {"<=$(=min({<Year={$(=min(Year))}>} Month))>$(=min({<Year={$(=min(Year))}>} Month)-1)"}>} [Trans Amount]) to give me the results of the first month in the selection. I selected May and Jun

The resulting chart correctly returns the correct cost for the first month in the selection May but Jun is returned NULL. I would have anticipated that Jun would also have returned the same value. Any thoughts?

[tanelry · Accepted solution]

In Pivot table you have much more flexibility to use chart inter record functions, like "before()".

For example you can get first column (MonthYear) calculated value equally in every column just like that:

before(sum([Trans Amount]),ColumnNo()-1) / before(sum([Trans Quantity]),ColumnNo()-1)

So in pivot table your expression could look like:

Sum([Trans Amount]) / Sum ([Trans Quantity]) -

before(sum([Trans Amount]),ColumnNo()-1) / before(sum([Trans Quantity]),ColumnNo()-1)

[#5]

Thanks Tanel,

That works.