Half To Date - Custom Measure - YTD Storage

[Former Member]

This is my formula:

MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])), ([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

MEMBER [MEASURES].[HTD_WORKING] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP"),IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="QUARTER",[MEASURES].[PERIODIC],IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="MONTH" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="WEEK" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/TILEVEL")="DAY",SUM(PERIODSTODATE([%TIME%].CURRENTMEMBER.PARENT.PARENT.LEVEL, [%TIME%].CURRENTMEMBER),[MEASURES].[PERIODIC]),NULL)),[MEASURES].[YTD])' SOLVE_ORDER = 3

This calculates the months correctly, but the 4th quarter calc is behaving like a QTD and there are no values in either the 1st half or the 2nd half:

NOTE:  I realize the 1st half for the Periodic is wrong - that is highlighted for something else, so please disregard that one.  I am focused on the HTD.

What do I need to do differently?  The half's are given a Level of Year, as there is no Half, that I am aware of (only Day, Week, Month, Quarter, and Year).

Your help is greatly appreciated.

Jeff

[former_member186338]

Hi Jeff,

I remember that somebody here managed to achieve some results, having YEAR as YEAR, HALFYEAR as QUARTER, QUARTER as MONTH, MONTH as DAY....

Vadim

[Former Member]

Vadim,

Thank you for your response.  Are there any other ramifications of changing the Levels for the different time periods?  I am not sure how this is used throughout the system.

Jeff

[former_member186338]

Hi Jeff,

I also don't remember the possible issues, but for sure the Year and HalfYear members have to be on different levels!

Vadim