Multi year YTD analysis using current member variable in BW 7.5

[former_member580444]

Hello Everyone,

I am faced with a tricky reporting requirement which involves the use of current member variables in a BW Query.

Requirement is quite simple. Suppose if the user runs a report with the Cal year month interval -> 01.2018 - 12.2018 then against each month in the row i am able to show YTD value.

But when i run the report with the interval -> 06.2017 - 07.2018, the YTD calculation should reset itself after the change of the year.

Kindly see the output attached to know the output. I am also attaching the technical detail of the YTD Keyfigure.

sdn1.pngIn the output you will see that the YTD values are displayed correctly from the month Jun 2017 till Dec 2017. But if you see the YTD value of Jan 2017 it adds up the value of Dec 2017 and Jan 2018. This is something that i want to avoid happening.

Kindly suggest if there is a way to avoid it.

Regards,

A Singh

[Loed]

Hi,

Were you able to get this without using the CALCULATION tab? Since I need to use the result of the cumulative YTD in a formula, so CALCULATION tab is not a solution.

https://answers.sap.com/questions/13468178/moving-ytd-which-overlaps-to-other-year.html

Thank you.

Loed

[cornelia_lezoch]

Hi Ashutosh,

I would probably not work with that selection that you used, but with simple key figure properties set in the query.

combine the cumulated calculation on your key figure with an exception aggregation and use 0CALYEAR and 0CALMONTH2 in rows instead of 0CALMONTH.

regards

Cornelia

[former_member567915]

Two things: First is that you YTD variable is not working as it should be, it should be only working one year at time. That brings to second point: Query Design approach. What is expected design for 1, 2 or more years? What kind of business rules must be followed to display it properly as expected? Does actual variables and methods capable to support it or should be modified/created new ones? Long story short, what you are displaying is a summarization for all available dataset, then to reach expected behavior to create a summarization each year, you must add calendar year to visualization and get a summarization by each year (Add 'Calendar year' and a higher level field (i.e Company), and for each one at rows section, select it and see 'Display' section @ properties, after ensure 'Result Rows' in such section are set as 'Always display'. Then when changed, it should be reset and summing up related records for same year only and an Overall Result at company level.

Other big thing is realign variables behavior to work in a standard way.

Hope it helps! Good vibrations!