selecting TIME period




How to select the yearly average value of a line item from a module that is dimensioned by time (monthly), for the current year, in another module that is not dimensioned by time? Please note, I wish to make the formula dynamic and not use static values like TIME.FY19 


I have a "Shipments" module that is having monthly scale, time dimension. Line item "ShipmentRatio %" is a percent formatted metric.


I have another module called "Yearly Assumptions", which is not dimensioned by time. 


In the Year Assumptions module, I wish to calculate a line item as follows:


CurrentYrAvgRatio = Shipments.ShipmentRatio %[SELECT: TIME.'FY19']

Please note that ShipmentRatio % Summary and Time Summary methods have been set to "Average"


I don't want to hardcode 'FY19'. 


Using Shipments.ShipmentRatio %[SELECT: TIME.'Current Period'] is not helping as it is giving me the first MONTH's value from 2019, instead of FY19 average.


How can this be resolved? 



Best Answers

  • LipChean_Soh



    You can create 2 line items, i.e.

    1. Year Selected with 'Year' as format

    2. CurrentYrAvgRatio = Shipments.ShipmentRatio %[LOOKUP: Year Selected]




  • BenHilgendorf

    To clarify @LipChean_Soh 's solution, the first line item that he mentioned (which should be added in the Yearly Assumptions module) should have the formula Parent(Time.'Current Period'). This will grab the current year. Then the second line item will use this to lookup the current year's Shipment Ratio average. 


  • This solution works. Thanks!

    I had the exact same setup, but what I was missing was PARENT(TIME.Current Period) instead of just TIME.Current Period to calculate SelectedYear. @BenHilgendorf noted that exactly, below