HOW TO SELECT HALF YEARS

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.?

Best 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

  • Thanks for your help
  • 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?
  • 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