Using PREV Across Categories

4.19K viewsFormulas and Functions
0

I’ve created a model that has years, quarters and months as categories in the time line.

To make it work, I need to enter most formulas 3 times, once for the base case and 2 more times to bridge between quarters and to bridge between years.

It seems to me that [PREV] (and [NEXT], etc) should work across category boundaries. Or am I missing something? If there is a reason for these not work across category boundaries, should there be a version that does (like [REALLY_PREV])?

Or is there a simper way to achieve this bridging that I’m unaware of?

Best,
Stefan

0

Hi – you are correct. Adding a second bridge formula would be necessary for the third time dimension.

There is another approach. Sometimes multiple bridge formulas can get a bit cumbersome to maintain particularly if you are looking to add quarterly or yearly summaries as the summaries can get in the way of any recursion formulas.

You can experiment with putting time on a single dimension and using groups, summary formulas and SELECT / USING AS functions to pull out the summaries. Steve Bailey, our Professional Services Manager, discussed this in this forum thread.

[url:307b7joq]http://www.quantrix.com/forums/showthread.php?t=477[/url:307b7joq]

Hopefully you will find this helpful. 🙂

-Mike

0

Thank you Mike. I assume that if I have month, quarter and year categories, I have to write two bridge formulas (one for quarters, one for years). Is that correct?

Best,
Stefan

0

Yes – you do have to write a separate formula to make the bridge – but you only have to do it once to make the connection.

Example – if you had a month and year categories… You would have formulas to carry forward the month data… and then a formula to carry over the Dec values of the previous year to January of this year.

Month[THIS]=Month[PREV]

Jan:Year[THIS] = Dec:Year[PREV]

Future versions of Quantrix may make this more automatic but this is the best approach for now.

-Mike

Latest Questions