HOW TO SELECT HALF YEARS

PrevContributor
PrevContributor
in Modeling

I know how to use the select function to select a time period e.g. SELECT [TIME. 'FY13']. However, I need to split my data by half years. Is there any way of selecting a half year or a group of months, e.g. Jan to Jun.?

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    Yes, you can select the total for H1 FY13, for example, by using: [select: time.'H1 FY13']
    This will select the total for the first 6 months of your fiscal year. 

Answers

  • PrevContributor
    PrevContributor
    Thanks for your help
  • PrevContributor
    PrevContributor
    Is there a way to select a half year that moves depending on which year you are in - so the model has 2013 and 2014, I want to select the HY but I want it to be HY13 in 2013 and HY14 in 2014?
  • PrevContributor
    PrevContributor
    Hi Laura
    You could use formula as below (built for month timescale)

    QUARTERVALUE(Value) + POST(QUARTERVALUE(Value), IF MONTH(START()) <= 3 THEN 3 ELSE IF MONTH(START()) > 6 AND MONTH(START()) <= 9 THEN 3 ELSE -3)

    You could also simplify IF formula if you build module (eg named Calendar) with month timescale and calculate in it the boolean property (Q1 or Q3)
    in this case you get formula

    QUARTERVALUE(Value) + POST(QUARTERVALUE(Value), IF Calendar.'Q1 or Q3' THEN 3 ELSE -3)

    Hope this helps

Categories