How to create a running total column in bex

[Former Member]

HI everyone,

i have a Sale Report in Bex, it looks like this:

         2013       2014      2015     2016

Sale: 50            50         60         50

now i want to create a dynamic running total for previous years until last year. it looks like this:

      running total until last year     2016

                    160                            50

and suppose when we have sale of the year 2017 (100), the running total will automatic add the sale of 2016 to it:

running total until last year

2017

                   210                          100

i have tried with bex but infact it doesn't work for me till now

Can you please help me how to do that?

Thanks

[Former Member · Accepted solution]

Hi everyone,

is there any Idea to do it?

[former_member585404 · Accepted solution]

Try by using selections

Selection 1 : set filter on year where year < Current year

selection 2 : set filter where year = Current Year.

Thanks,

Dinesh

[Former Member · Accepted solution]

Hi Dinesh,

Thank for your answear.

but what do you mean by "selection"?

Do you mean that i need to create 2 query with 2 selection in filter?

Thanks

[former_member241863 · Accepted solution]

Hello Pha London,

Dinesh is talking about RKF (Local RKF), In BEx Query Designer -> In Row/Columns Tab -> Key Figures -> Right Click on Key Figure and Create Selection.

Selection works as a local Restricted Key Figure (RKF).

and Filters in Selection means - Restrict the keyfigure with filter in Selection.

Can you share the current BEx Query's Rows/Columns screen shot.

Thanks

Pratyaksh Jeet

[Former Member · Accepted solution]

Hi Pha,

this is an example RKF

In this example the restriction will work for months < 12.2016. This will be one of the columns you need. the other column should be > 01.2017 for instance.

Of course you will need to do it for year.

Hope this helps.

Regards from MX.

[Former Member · Accepted solution]

Hi Gonzalo,

thankyou very much for the Reply. i understand your Solution but i want a dynamic year. that mean i want a column for year < current year and other column >= current year.

and the current year can be changed depend on the data in the system.

[former_member200967 · Accepted solution]

Hi Pha,

Can you create a user exit variable which calculates the current year. you can use that variable in the first RKF (for current year sales) and you can also use the same variable(storing the current year) in the second RKF with restrcition of Less than equal to( see the screenshot below).

(PS: Here the USERENTRY variable value i am using is being input the user , hence the name )

Regards

Manpreet

[former_member585404 · Accepted solution]

Hi,

If you are using 0CALYEAR in the selection

Variable 0CYEAR on 0CALYEAR gives current Year

selection 1 contains : Key Figure + 0CALYEAR(=0CYEAR)

selection 2 Contains : Key Figure + 0CALYEAR(<=0CYEAR)

if you are using Z* Objects then you have to create a Customer Exit Variable to get current year and do the same mentioned in the selections.

[Former Member · Accepted solution]

Hi Pratyaksh,

My report looks the same like this

[Former Member]

Hello Pha,

I think the request is clear (your screenshot is very clear),

also I think the solution is clear and you have two options: 

1. If you are using RKF you can check the comment of Dinesh Kumar Reddy Basi Reddy

    2. If you can use ABAP Code you can check the comment from Manpreet Singh

Regards.