Retrieving YTD value when timescale spans multiple years

SEllis
SEllis
in Modeling

Hello,

 

I am having trouble with how to model for multiple time period aggregation combinations.  Using units as an example, I want the units for the individual time periods, quarters, and years, however I also require the QTD, YTD, and YTG (Year to go).  What is the best practice to incorporate this into a module?  I understand that this increases the cell count (and ostensibly the sparsity).

 

A few options that I am working through:

  - A way to have the above as a context list (pivot point) on a module which would facilitate different time aggregations in the same time period.  This presented the complication of telling the module function how to calculate based on this selection (not sure how to do that yet).

 

I have been able to work around this as a combination of list items, such as Units, Units QTD, Units YTD, etc.  This doesn't seem like an effective approach, wanting to know if there is an alternative option.

Tagged:

Answers

  • DavidSmith
    DavidSmith

    Hi - You are correct in that you will need more line items.  The YEARTODATE function will reset itself each each so you will be able to see the rolling YTD, across years (e.g. 2016, 2017, 2018), so if understand your requirement correctly so should be able to compare March-17 YTD vs March-18 YTD.  The QUARTERTODATE function will also so the same, resetting each quarter.  You can calculate the YTG for each month by using the formula YEARVALUE(units) - YEARTODATE(Units).

    You could use a filter module to "set" the month parameter (for March, as above) and reference this against a time module.  So have a generic list Jan-Dec with attributes 1-12.  you can then check that against each month using Filter Month.Month=Month(start())

    Does that help?

Categories