Is it possible to calculate YTD / YTG total?

[Former Member]

Hi,

I have a planning view consist of a calculated key figure that is partially actual and partially forecast by comparing PERIODID with $$PERIODIDCU$$.

Is it possible to create 2 local member columns at the end to calculate the Year To Date (YTD) and Year To Go (YTG) total?

Basically the YTD part will be all the actual data for which PERIODID < $$PERIODCU$$ and the YTG part will be forecast data for which PERIODID >= $$PERIODCU$$.

Is this something we can do at the template level with local member if both storage and display buckets are in weeks (most granular)?

If the above question is possible, how about the planning view has storage bucket as week and display bucket as month?  Would it be possible then?

Thank you.

Yee Ann

Message was edited by: Alecsandra Ghita

[former_member217157 · Accepted solution]

Hi Yuen,

    Actually, you can try creating YTD and YTG Key Figures in the Planning Area

Check if this would work:Assuming your TP has Mth,Qtr Yr,

and your Key Figure KF1 is at MTHPROD level

1.Set to NULL all values for KF1 hat dont belong to current year and future periods of current year

HKF1@MTHPROD = IF(PERIODID1 = $$PERIODID1CU$$ and PERIODID0 <= $$PERIODID0CU$$, KF1@MTHPROD,NULL);

2. Aggregate to Yr from Month

HKF1@YRPROD = SUM(HKF1@MTHPROD) ;

3. Put the aggregated Yr value to the current month

KFYTD@MTHPROD =  IF( "PERIODID1"  =  "$$PERIODID1CU$$"  AND  "PERIODID0"  =  "$$PERIODID0CU$$"  ,  "HKF1@YRPROD"  ,  "HNULL@MTHPROD" )

where HNULL is a KF which just has NULL values at MTHPROD level. You can create this KF as HNULL@MTHPRD = NULL (I/P KF1@MTHPROD)

There may be variations of this, but this in principle should work

Thanks,

Raghav

[Former Member · Accepted solution]

Thanks Raghav,

I think I am following....

A couple of questions, primarily on the planning level you mentioned, i.e. @MTHPROD and @YRPROD:

• If my regular planning level is @PERPROD which has "Week" as root, in order to implement your recommendation, do I create additional planning levels @YRPROD with "Year" as root for step #2 or it will be represented by @REQUEST?

• For step #3, would system allow planning level on the right (@YRPROD) not equal to planning level on the left (@MTHPROD)?

• For step #3, can HNULL@MTHPROD be replaced with a "NULL"?

Thank you.

Yee Ann

[former_member217157 · Accepted solution]

Hi Yee Ann,

My response below.

• If my regular planning level is @PERPROD which has "Week" as root, in order to implement your recommendation, do I create additional planning levels @YRPROD with "Year" as root for step #2 or it will be represented by @REQUEST?

->You need to create a new planning level with Year as root 

• For step #3, would system allow planning level on the right (@YRPROD) not equal to planning level on the left (@MTHPROD)?

->  Yes as long as you have a Key Figure at MTHPROD as additional input.

• For step #3, can HNULL@MTHPROD be replaced with a "NULL"?

-> Actually yes, but I think UI does not allow this (Give it a try). So you need to create a HNULL KF.

Hope this helps,

Raghav

[Former Member · Accepted solution]

Hi Raghav,

Yes, it works!

Seems to me that the purpose of having HNULL@MTHPROD is to let enable step #3 to include the planning level @MTHPROD as input.

If I want to have multiple YTD calculation, can I reuse the same HNULL@MTHPROD by adding additional input key figures?  is there any drawback of doing so?

Thank you.

Yee Ann

[Former Member · Accepted solution]

Hi Yee,

Can you share the screenshot of how you configured HNULL@MTHPRD = NULL (I/P KF1@MTHPROD)

What does "I/P" mean?

@Raghav - Does on-premise support "NULL" value assignment?

Regards,

Aditya G

[former_member217157 · Accepted solution]

Hi Yee Yuen,

You can use HNULL KF for other YTD calcs. You dont need to include additional input KFs. Any one  time-dependent stored kf which does not have missing time periods is an input to this KF.

Thanks,

Raghav

[former_member217157 · Accepted solution]

Hi Aditya,

HNULL@MTHPROD = NULL. Then click on Key Figure Input Dialog button and choose any KF which has time periods filled. eg KF1@MTHPROD.

This should also work for On-Premise.

Thanks,

Raghav

[Former Member · Accepted solution]

Thank you very much Raghav,

I have done further experiment on this topic, please allow me to ask a couple of follow-up questions:

1) Step 2 Aggregate KF value to Yr from Month, you suggested the follow

          HKF1@YRPROD = SUM(HKF1@MTHPROD)

I tried another variation - it seems to be possible to also use time independent planning level @PROD to do the aggregation, and that would allow Step 3 to use just NULL at the end without creating a helper HNULL@PERPROD

For this aggregation step, between using @YRPROD and @PROD, does one better than the other?

2) Step 3 is to put aggregated Yr value to the current month

it seems that the YR comparison part (i.e. "PERIODID1"  =  "$$PERIODID1CU$$" ) can be omitted as long as PERIODID0 check is there, would your agree?

Thank you.

Yee Ann