Get last month revenue

[#1]

Hi All

I´m having a issue in calculating a key figure that lookes like this "some amount / (this month revenue + last month revenue / 60)". The key figure is to be used in a line chart where month of year is the dimension.

It is no problem getting data for "Some amount" and "This month revenue", however I can´t figure out how I get the revenue from the last of the current month you are currently calculating.

My data is pre-aggregated to one record pr. month, so "Last month revenue" is always just one row. Don´t think that makes any difference, but now you know ;o)

Hope one of you can help me

And happy hollidays by the way ;o)

/Daniel

[swuehl · Accepted solution]

Daniel,

one possible solution might be using chart inter record functions, like above().

If you have a chart with dimension month of year and you get the revenue for a month with an expression like

=sum( MonthRevenueField )

then your expression could look like

= sum( SomeAmountField) / ( sum( MonthRevenueField ) + above(sum( MonthRevenueField))  / 60 )

(Not sure about the correct use of brackets around the division here, I tried to copy from your above post).

Above function will evaluate the expression inside in the context of the dimension one row above (in a line chart with dimension month of year, this should be the previous month).

For the very first month of year, you won't get a value for the previous month this way, so this might be a problem.

Regards,

Stefan

[#3]

Thank you swuehl... Exactly what I was looking for..