Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to calculate the raise rate like: compare revenue between this year and last year, it looks like this:
Year Revenue Rate
2007 $100 -
2008 $150 50%
How can I achieve it?
I appreciate your suggestion. Thanks.
Isaac Li
Hello Isaac,
Having that data is displayed in a pivot table, you can use the following
([Revenue] - below([Revenue])) / below[Revenue]
Depending on how you have your fields in yart, you may need to use after() ir above() instead of below().
Anyway, look for further documentation in the Reference Manual, Inter-record function()
Hope that helps!
You can also handle it with data. If your data is actually stored as shown, just do something like this during the load using the previous() function.
Year, Revenue, LastYearRevenue
2007, $100,
2008, $150, $100
If the revenue is calculated, you could generate an AsOf table to connect things together:
AsOfYear, YearType, Year
2008, Current, 2008
2008, Previous, 2007
2007, Current, 2007
2007, Previous, 2006
...
And build a chart something like this (modified for however you calculate Revenue)
Dimension = AsOfYear
Revenue = sum({<YearType={'Current'}>} Revenue)
Rate = Revenue / sum({<YearType={'Previous'}>} Revenue) - 1
Search for "AsOf" on the forum for some examples.