Calculated YTD in SAP BW Backend

[former_member205188]

Hi All,

Have a client requirement where we need to calculate YTD without variable input screen.

User wants Calendar Month as free characteristic, where he will apply filter on any of the month (between April - Current Month) and YTD should get recalculated from April to selected Month.

Kindly provide your valuable suggestions to achieve this.

Regards,

Manisha

[Former Member]

Hi Manisha,

If there is any Selection variable based on Time Characteristics, customer exit variable can used to get the cal month, Quarter, Fiscal Year etc.

Derive the Fiscal Year and Calendar Month from Selection Variable Value.

Define Structure on Selection

MTD - Restriction on 0CALMONTH = Your Customer Exit Variable to derive Cal Month.

YTD - Restrictionon 0FISCYEAR = Your Customer Exit Variable to derive Fiscal Month .

Using Cal Mon as a Free char you can get YTD but you will not get MTD at the same time.

Regards,

Sucheta

[former_member205188]

Hi Koen,

That is the issue.

User wants both in the same report and without selection variable.

Thanks a lot for your help and guidance.

Regards,

Manisha

[former_member205188]

Hi,

Discussed with the end user and resolved it by taking user input for calendar year/month and calculating the YTD accordingly.

Regards,

Manisha

[kohesco]

Hi,

Could this work for you ?

- create a restricted key figure (YTD) restriction: Current year

- WHen the user is filtering, he has to set the filter as range, Greather Than (april)

[former_member205188]

Hi Koen,

Let me check this and get back to you.

Regards,

Manisha

[former_member205188]

Hi Koen,

It is not working can you help me with some other solution.

Regards,

Manisha

[kohesco]

can you show me some screenshots of your result and what you want to see, please

[former_member205188]

Hi Koen,

Below is the screenshot of my requirement.

When the report is launched revenue YTD should be displayed from start of fiscal year till current month. i.e. from April to September.

After this user can apply filter on Calendar Month available under free characteristics.

At this time, revenue should be displayed from the start of fiscal year till the month selected by user.

Example, If user selects July, then revenue YTD should be from April to July.

Regards,

Manisha

[kohesco]

now I understand clearly.

in characteristic restrictions set: >= april

in default restrictions et <= september

the default you can change after the report has run to <=July

note that they have to set variable range "<=" as well

[former_member205188]

Hi Koen,

Thanks for your reply.

Sorry for missing out on 1 point.

User along with the YTD of Revenue, he also wants Revenue as on current month.

Taking the same above example, If user selects July, then revenue YTD should be from April to July and revenue should be for the current month i.e. July.

If I apply the logic provided by you, then even for the revenue column I will get data from april to current month.

Regards,

Manisha

[kohesco]

Hi,

the revenue column should als be a restricted key figure with definition:

• revenue
• Greather Than or Equally calmonth

The default restriction will restict the entire query as Less than or E. so, only the equally calmonth will be shown in your output.

Grtz

Koen

[former_member205188]

Hi Koen,

Thanks for your response. I have 1 query.

You said - "Greater than or equal to calmonth"..

By this you mean restrict with calmonth variable?..

I have tried this and revenue column is not displaying the value.

Regards,

Manisha

[kohesco]

Hi,

The same way of restriction as your default restriction!

grtz

Koen

[former_member205188]

Hi Koen,

I have applied the restriction the same way as default restriction.

Attached the screenshot for your reference.

Kindly check the screenshot and let me know, if I have missed out any step.

In the filter screen shot: When I am changing/applying the filter value as <= 6.. Then YTD is coming properly as Summation from April - June.

The revenue column should display value as on June, but it is not coming.

Regards,

Manisha

[kohesco]

you have to use variables with user input, not hardcoded values, you can not change the hardcoded values

apologies I didn't say this clearly

[former_member205188]

Hi Koen,

I have used the variable only, changed it while giving the screenshot.

I have created a cmod variable that returns current month.

But as I mentioned above even if I change the filter value for calmonth the revenue column is not reflecting the data of selected month.

Though YTD is getting calculated properly from April till selected month.

Just stuck up at this one last step, on how to get revenue column populated with the data of the calmonth selected in the filter.

Regards,

Manisha

[kohesco]

you should use user input variable, the default value you can set as a cmod value, but the variable should be user input, a cmod variable is lika a hardcoded value

[Former Member]

Hi Manisha,

Is there any other date variable being used in your requirement?

Regards,

Vikrant

[former_member205188]

Hi Vikrant,

We have Calendar Month, Fiscal Year and Fiscal Period at data level.

User will be filtering based on Calendar Month.

Regards,

Manisha