Two cumulative values in expression

[#1]

Hi all,

This is probably something pretty obvious, but I didn't find anything with search. I have a table with the values Year, Month, Cost and Quantity and I want to create a chart with Month as the dimension and a cost per unit (from Cost & Quantity) since the beginning of the Year as an expression. So basically it would mean having cumulative Cost divided by cumulative Quantity.

Plotting or creating straight tables of cumulative values is pretty straightforward, but is there any way to actually reference the calculated values? Using the field name in an expression passes the code and not the value, losing the cumulative part. Or am I looking at this in the wrong way?

[swuehl]

Hi,

not completely sure what you are looking for, but you might want to take a look at the inter record functions, especially

column(ColumnNo) to reference the value of that column in the same row.

Regards,

Stefan

[#3]

Hi,

Thanks for the quick reply! Using column() I get the same problem though. I added the columns SumCost and SumQty that have accumulation turned on. If I reference them using column() it ignores the accumulation and gives the single calculated value for that field (and not what it actually shows in the table).

[swuehl · Accepted solution]

Hi,

you are right, haven't noticed that before. That's probably why I seldom use the built in accumuation and do it manually.

For example

you could use (without full accumulation!) this as expression for cumulated Cost (in this example, you need to label the expression as cumCost)

=Rangesum(sum(Cost),above(cumCost))

and equivalent for cumulated quantity.

Then you could reference the values with column( ) and do an easy division.

Regards,

Stefan

Nachricht geändert durch swuehl