Calculate Total Value based on the User Input % inthe Integarted Planning WorkBook

[Former Member]

Hi Team,

I wanted to start a new conversation with different subject as it will be easy to find when people search on this.

I have the planning query, currently I have hardcoded the fiscal year period dates for testing ( plan is to use the customer exit later),the query allows to user input the sales amount at a account level(based on the hierarchy).

But the user also want to calculate the final value using above user input sales amount * %(user input).

Can you please advice on how to proceed on this? Are there any documents that I can reference

Below is the example:

Account       Fiscal Year Period

                      001.2015               002.2015

A                       15000                   10000 ---- Rolled up to node level

--A1                  10000                   5000 ----- User Inputs

--A2                    5000                   5000------User Inputs

25% --- User input

Total Sales = 10000(Rolled up to node level value) * 25% (user input %)

How can I achieve this using the IP within the workbook?

Thank you.

[cornelia_lezoch]

please read SAP help about planning functions and how to implement them.

[Former Member]

Thank you.

I read about the revaluate function but what I'm trying to achieve is slightly different because based on the planning values(user input)  at a detailed level hierarchy , the %(again a user input)  should be used to get the total sales value at higher level hierarchy in another query on the same workbook.

So this can be achieved using the planning function?

Thank you.

[Former Member]

Hi All,

Can anyone please provide some details on how to achieve the above requirement?

Your help is highly appreciated.

Thank you.

[gregor_dieckmann]

Hi,

your requirement is unclear:

- What you want to achieve with the user input of a percentage value? Percentage of what? Is this a revaluation factor? Do you want to store this percentage value?

- If you revalate the (rolled up) amount for A, what should happen with this value that you call 'Total amount'. Is this just a formula? What happens with amount values on A1, A2, do you want to disaggregate 'Total Amount' from A to A1, A2?

Regards,

Gregor

[Former Member]

Hi Gregor,

Here is what I'm trying to achieve.

I have a planning query based on the detail level hierarchy in rows and amount in columns.

Currently through this query the user can input the plan data for the current month onwards and all previous months are actuals.

I have another query based on the same multiprovider but different agg. level.

This query is at a high level hierarchy (rows) and the amount (which is a total of above detail level lines by category)

So the plan data for Query 2 should be using the individual % values (that user would like to input for each category) and then using that %,revaluate and save that new value as plan data at high level hierarchy.

I'm looking for help on how to provide the ability to users to input the % values and use them for calculation the totals amount at high level hierarchy.

Please see the attached screen shot for details.

[gregor_dieckmann]

Hi,

it seems that this is not a revaluation use case ( take a value and increase/decrease it by a certain percentage) but a disaggregation use case where you want to give the user the ability to manually maintain the disaggregation weight factors (the second table in the screen shot).

And this might already be the solution:

1 use an input-ready key figure to be able to maintain the disaggregation weight factors (should sum up to 100%)

2 disaggregate the amount with respect to the key figure in 1 (this is just a query setting); you may hide the key figure with the disaggregation weight factor in tables 1 and 3 and display only in table 2 in you screen shot.

The question is whether the weight factors are valid for all periods or may change for each period, but this does not invalidate point 1,2.

Regards,

Gregor

[Former Member]

Hi Gregor,

Thank you for quick response with details.

So based on my understanding the following should be done?

2. Create a new infoobject (key figure - Zfactors)
3. Add it to Cube (real time cube), MP,AL, Query.
4. Make it an input ready.
5. Disaggregate the Amount based on the Zfactors in the input ready query.
6. Display or hide the Zfactors based on the requirements.

Where to limit the factor value to 100%?

Also ,based on my requirement above  ,I dont need to add the Zfactors in input query1 correct?

really appreciate your help.

Thank you.

[gregor_dieckmann]

Hi,

yes, your understanding is correct.

Whether to include the weight factors in the InfoCube or in another InfoProvider (like a DataStore Object with another granularity) is another question; this is why I asked whether the weight factors should also depend on the period. But this does not invalidate the basic idea.

Unfortunately, there is no build in way to ensure that the weight factors add up to 100%; you may use a reporting exception but this only gives some visual feedback but one is still able to save data. One might write a validation planning function that runs before save to check whether the factors are ok.

If the query1 is on the lowest level and there is not disaggreation you don't need the weight factors there.

Another modeling option just comes to my mind: use an input-ready formula %GT (or %XT, %YT) if only some detail values have to be adjusted after disaggregation according to a special percent value. But here the % values will not be saved. %XT, %YT exist only in BW 7.40, cf. the documentation (percentage with respect to the next sub total level on X or Y axis, respectively).

Regards,

Gregor

[Former Member]

Hi Gregor,

Thank you.

As per the requirement, the users may or may not use the same % values every month.

So, I was thinking to store them and so when they open the Analysis planning workbook next time, it will show the % values?

Also I think I might need to create another infoprovider (real time) to store these factors and also the totals at a highler level (at category level).

Because the current infocube (actual) is at the detailed level and so the real time cube for input query1 is also at a detail level.But If I need to achieve the factors and query 2 in the screen shot then do you think I need to create another real time infocube?

I'm not sure if using the 2 actual infocubes and 2 real time infocubes and a MP on top of it and AL on top of it is possible or advisable?

Please advice on the approach on saving back the data.

Thank you.

[gregor_dieckmann]

Hi,

having 4 part providers in a MultiProvider is no problem.

If you store the weight factors in other InfoProvider you have take care that disaggregation still works as expected since disaggregtion will be done for 'Amount' using weight factor from another

InfoProvider. If you have different level of granularity disaggregation may not work as expected since disaggregation creates a special 'list' with all characteristics drilled-down contained in the aggregation level (not restricted to a single value). If the disaggregation reference has less characteristics compared to the InfoCube with amount (not having a single value restriction) then the 'disaggregation list' looks like a chess board and the weight factors are at a wrong place, cf. note 994853, attached PDF document, section 2.3.2.4.

Remark:

You cannot store data at different levels in one InfoCube; technically there is only one level and if e.g. an aggregation level has less characteristics than the InfoCube data will be aggregated to that level. In this sense an aggregation level is a view, not something to store data in.

Regards,

Gregor

[Former Member]

Hi Gregor,

Thank you.

That's exactly what my fear was.

So the scenarios like this cannot be achieved with IP or any other planning tool unless the data is available at a category (or at a high level) in the actual cube?

The information you provided has cleared lot of questions that I had and was highly helpful.

Thank you.

[gregor_dieckmann]

Hi,

I don't get your point.

I only wanted to point out that having e.g 4 characteristics in the planning provider IC, only 3 of them in a provider W that has the weight factors, how should it be possible to disaggregate to all 4 characteristics using weight factors from W?

But what you can do is to store the weight factors in IC as well and create an aggregation level with only 3 characteristics and maintain the weight factors there and disaggregate the weight factors as well (e.g. with same distribution). Then weight factors are available at the level you need it though end user may only need a higher level to maintain the weights.

Regards,

Gregor

[Former Member]

Hi Gregor,

Sorry I'm little confused now. But just wanted to make sure my understanding is correct here and will also try to explain the current state so its clear.

This is what I have for

Input Query 1 from the screen shot:

1. Actuals infocube with data at detail account level.
2. I created a real time cube with same level as above
3. I created a multiprovider on top of above cubes
4. I created an agg. level on top of MP above (agg level only has the required detail level dimensions for input query 1)
5. I created an input query.
6. The input query works as expected allowing the users to input data at detail account level hierarchy and save it back to real time cube.

Weight Factors: As per our conversation

1. I will create a new info object (key figure - Zfactors)
2. Should I add this to the current real time cube?
3. Add it MP.
4. Create a new AL with high level hierarchy using the same real time cube as above? The problem is I cannot exclude the detail account level hierarchy from this agg because the filter is applied using the detail level hierarchy. This is where I don't understand as how I can achieve this?
5. Make key figure an input ready
6. Use this as reference object to disaggregate the amount. But main issue is with the detail level hierarchy in the agg level cannot save these factors at higher level of hierarchy.

Query2:

1. If I'm successful to save those factors at higher level of hierarchy(at category which is a navigational attribute) then the disaggregated amount can be saved without any issues.

The main issue is how to save the weight factors and amount at a category level (high level)without excluding the detail level hierarchy as I need it for filters?

Thank you.

[gregor_dieckmann]

Hi,

I see that here it is hard to have the complete picture since from this thread not all (mabe necessary) details are mentioned. In your description you talk about a MultiProvider but on the other hand only about one InfoCube ('actuals infocube') where you also store the plan data?

Let me start with some facts:

- if you want to filter values according some characteristic this characteristic has to be in the query

- the aggregation level is used to define at what 'logical' level the data are written back into the InfoCube, all characteristics not in the aggregation level are empty (forget about derivation for now). Reading data with respect to the aggregation level means to aggregate all key figures with respect to characteristics not contained in the aggregation level.

Lets make it simple:

1. To write data back to the InfoCube via planning you define an aggregation level that defines how granular you want to plan the Amount. Since you want to disaggregate to this level with 'customized' weight factors you also need a key figure with weight factors at this level.

2. Thus include the key figure for the weight factors in the InfoCube.

3. If end user should be able to maintain the weight factors at a higher level just create a query for this based on the aggregation level in 1; expose only the characteristics needed for the weight factors as free characteristics (or drilled-down in rows or colums). Use disaggregation for the key figure that contais the weight factors. This means - in fact - that the weight factors are more detailed than you see in the query.

4. To plan the amount still use a query with aggregation level in 1, disaggregate 'Amount' with respect to the weight factors.

As a result, this model is simple, it just uses one InfoCube and one aggregation level. Different query views are used to plan 'Amount' and maintain the weight factors. But all values will be 'stored' according to the one aggegation level.

Just play around with these design ideas. I am sure - if adjustments are necessary because of facts not discussed in this thread - you will be able adjust the above design based on the ideas above.

Regards,

Gregor

[Former Member]

Hi Gregor,

Thank you for the detailed explanation.

No ,I have two cubes (one is actual and one real time) and on top of this I have the MP and Agg level is on MP and the input query1 is on Agg Level.

So based on all the conversations we had ,I think the second query DOES NOT require to be input query.

The weight Factors should be added to the cube and simply use these weight factors to disaggregate the amount using a second query to display the QUERY 2 output .

Sure, I will try different things with this concept/approach.

Your help and information is greatly appreciated.

Thankyou.