2nd half yearly values

[sap_user62]

Hello Friends,

BPC 10.1 standard on hana/bw7.5

What is the best way to get cumulative values of last 6 months of data starting from month number 7 and depending on which month is refreshed in the report. (Model is periodic)

example - data refreshed for period 8, data should show for period 7 + period 8 cumulative

fiscal year - begins in May. I have attached the screenshot of time dimension.

I created a custom measure but it gives prior 6 months of data for any month chosen.

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[%TIMEQUARTERLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]) + SUM(LastPeriods(6,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]),

IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" ,SUM(PERIODSTODATE([%TIME%].[%TIMEQUARTERLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA])))';SOLVE_ORDER=3

[former_member186338]

Please use "Insert Image" instead of incorrect "Insert File"

Also explain requirements for the first half of the year.

[sap_user62]

For the first half of the year, standard YTD measure is sufficient with selection of current time period in context for any first 6 months

But for the 2nd half we don't have any standard measure which gives us cumulative data starting month 7.

This is a requirement for half yearly reports.

[former_member186338 · Accepted solution]

Why not to create half year parents (with the level WEEK - recommended in SAP note) and create a measure like QTD?

[sap_user62 · Accepted solution]

I referred to this note. 1744366 - How to configure half year node in Time dimension. - Let me know if you are referred to another note, I will give it a try.

and following Step B. (Note mentions , you can either use Step A or Step B)

STEP B: Change PERIOD property of TIME dimension.
- Find all half year members like XXXX.H1 and change its value of PERIOD property to Q1 or JAN.
- Save and process the dimension.

- Please make sure the property is not referenced in your script logic, member formulas or business rules. Otherwise, this modification may impact these business logics or rules.

Pulled the member on the report, no data. I did not create additional measures.

[former_member186338 · Accepted solution]

sap.user62

Sorry, but your PARENTH1 is completely incorrect!

Both H1 and H2 have to be created.

Then

Q1,Q2 -> PARENTH1 2020.H1

Q3,Q4 -> PARENTH1 2020.H2

2020.H1-> PARENTH1 2020.TOTAL

2020.H2-> PARENTH1 2020.TOTAL

[sap_user62 · Accepted solution]

Thank you Vadim for your suggestion. I have updated the hierarchy accordingly.

H1 is a sum of Q1 and Q2 and H2 is a sum of Q3 and Q4 and is reflected properly in the reports

In the standard reports, we select the open month in the context and use periodic or QTD or YTD measures to show monthly, qtd and year to date data.

With this approach, if the year is complete (there is data for all 12 months), is it possible run a report for H2 data for period 10 (it has to be the sum of period 7,8,9,10).

Do we have create additional measure here.

[former_member186338 · Accepted solution]

sap.user62

Happy New Year!

For sure, you have to create a custom measure using logic similar to QTD: For example HTD

I have mentioned it in my original answer.

[sap_user62 · Accepted solution]

Thanks Vadim for your reply and a very happy new year to all of you.

This is the custom measure created for HTD.

INC part is updated for now.

Our users run reports based on Open month selection in the context and measures in the columns, so they usually don't select 2020.Q1 or 2020.TOTAL, instead they select 2020.09 in the context and Periodic , QTD and YTD in the columns, so I would assume even if we don't create .H1 and .H2 in the time dimension, for our current client it would work.

Let me know if there are implications, and if we can further optimize the measure.

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")<"007",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),


IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")="007" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")="008" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")="009" ),SUM(PERIODSTODATE([%TIME%].[%TIMEQUARTERLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),


IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")="010",SUM(LastPeriods(4,[%TIME%].CURRENTMEMBER),-[Measures].[/CPMB/SDATA]),


IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")="011",SUM(LastPeriods(5,[%TIME%].CURRENTMEMBER),-[Measures].[/CPMB/SDATA]),


IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" AND [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/BASPERD")="012",SUM(LastPeriods(6,[%TIME%].CURRENTMEMBER),-[Measures].[/CPMB/SDATA]),


IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%RPTCURRENCY%].CURRENTMEMBER.PROPERTIES("2/CPMB/JPPF81I")<>"Y",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),
IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP" AND [%RPTCURRENCY%].CURRENTMEMBER.PROPERTIES("2/CPMB/JPPF81I")="Y",SUM(PERIODSTODATE([%TIME%].[%TIMEYEARLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),
IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),
IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),
-[MEASURES].[/CPMB/SDATA])))))))))';SOLVE_ORDER=3

[former_member186338]

I have told you - use QTD as a template for HTD. Instead of %TIMEQUARTERLEVEL% try to use

%TIMEWEEKLEVEL%

'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",SUM(PERIODSTODATE([%TIME%].[%TIMEWEEKLEVEL%], [%TIME%].CURRENTMEMBER),-[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(PERIODSTODATE([%TIME%].[%TIMEWEEKLEVEL%], [%TIME%].CURRENTMEMBER),[MEASURES].[/CPMB/SDATA]),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="AST",([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ",-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))';SOLVE_ORDER=3

You current formula may be very slow.

[sap_user62]

Thanks Vadim.

There are two things that I have noticed

1. I used your suggestion earlier to replicate QTD formula and replacing it by Weeklevel, i got this error [ "Dimension TIMEWEEKLEVEL does not exist in Management (Member formula)], I am not sure if further config was needed.

2. I did not update the existing standard formulas for periodic, qtd or ytd. Reports show incorrect values for standard QTD measure, it looks like it has taken over Half YTD formula.

[former_member186338]

sap.user62

Try to use fixed level:

Instead of [%TIMEWEEKLEVEL%] use [LEVEL01] or [LEVEL 01] for HANA.

Try 01, 02...

https://launchpad.support.sap.com/#/notes/0002164827

And looking on your test results - standard QTD is now HTD, meaning that TIMEQUARTERLEVEL is now half year level.

Play with fixed levels.