How to Move Qty in Bex query from present month to 'N' month before

[Former Member]

Hi All,

Is there any way I can do some formula in BEx query to achieve best result for my issue.

I want to shift qty from one month to another month based on lead time (in months).

example : I have lead time '3' then my quantity shift always 3months ahead if do not have history 3 month then shift to 2 month.            

                 if do not have 2 month before then shift to 1 month before .

                if do not have any previous month display then no shifting show in same month.

is possible in BEx Query.

explanation:   first month demand 40 qty but even lead time 2 there is no previous month display so required same month '40'

                       second month 30 qty  also lead time 2 but only one month have previous so add to previous month required so added.

                        third    month demand 20 qty also lead time 2 so two month before means 1st month so added to required first month .

                       4th month deman 20 lead time 2 so two months ahead required so that 2nd month updated with 20.

                                                                    months

                   lead time                         1          2        3         4

                           2          demand       40       30      20      20

                                        required      90       20

Now I want formula to achieve  Required qty, is there any way we can dynamically get when BEx query run .

if not understand will explain again.

Thanks and Regards,

Harikiran.

[Former Member · Accepted solution]

Hi Yasemin,

Thanks for the advise. I have handled in End Routine with ABAP Logic.

I achieved using different formulas and control statement in that. It is hard to show here. Really anybody have the same requirement can ping me. I try to help.

I have done carry forward monthly based at the same time based on lead time (in months) calculated PR(purchase requisition ) quantity.

Thanks to all for your support.

Thanks and Regards,

Hari

[Former Member]

Hi All,

Nobody answer to those question, Not yet answer to any of my questions.

It looks Sdn is not responding properly.

In this case how I can believe get answers from the blog.

Thanks ,

Hari

[yasemin_kilinc]

Hi Hary,

Noone answering your post does not mean that SDN is not responding properly. You shouldn't blame anyone about this. All the members in this community tries to help others when they have an idea. And they do that without any expectation of getting a benefit. Your issue is a very complex design case. Members may not have an idea on that. You should remodel all the scenario and maybe create another datasource with a function (by an abap expert) where you get the required output.

I can see that Loed had done everything he can, without any motivation of getting points and even then you can talk about "noone answers!?!?!?!".

I think Loed deserves an apology for your post having the sentence "nobody"

Regards

Yasemin...

[Former Member]

Hi Leod and Yasemin,

thanks  for the update . Please see my words I did not say no one responding.

I say no body respond with proper answer . If you understand wrong way I am sorry for that.

One thing SDN is already well established blog only Leod reply  ... That's great...

Means even anybody can suggest at least any way something  todo in BEX .

I am looking on BI side something can do, so that it makes easy to build rather it do on ABAP.

At present I have done ABAP code in END ROUTINE.

But expect some BI EXPERTS help on BEX..

I Am sorry Leod if you hurted with my words.

somebody put like for your post above but they even didn't reply something for this post...

hope you understand .

Thanks,

Hari

[yasemin_kilinc]

Hi Hari,

Once more, when you can't get a response for a post, this could mean that nothing comes to our mind to solve your problem. In you case, I really can't think a better approach then doing it in ABAP coding. That is whay others don't response. If you have found a solution, that's great. Please share it with the community and close your thread.

Regards

Yasemin...

[Loed]

Hi Hari,

What's your INPUT VARIABLE? Is it lead time and calmonth? Or lead time only and calmonth always starts from january of current year? Or is your lead time fixed to 3?

Regards,

Loed

[Former Member]

Hi Loed,

calander month  demand quantity push to 'n' months early and show

in required qty.

here lead time = 'n' in months varies from 0 to 6 (I have taken lead time as

numeric character(NUMC))

caledar/month is calendar month 1-12

demand quantity is key figure.

required quantity  is key figure.

hope you understand. If you have any logic update me.

Thanks,

hari

[Loed]

Hi Hari,

What I mean is, how will I know the lead time? Is it a variable where user enters the lead time of their choice? What are your variable input for your users? Like do you have a variable for calmonth?

Regards,

Loed

[Former Member]

It is actually MRP report, User key in only MRP Upload date and Plant. I am getting all information like lead time in months, demand qty etc from my customized data sources into MP. I have to calculate PR(purchase requisition) qty and show in respective month based on lead time month. Example: 1. I have lead month 2 , Now it is May my demand qty ( 30 in June , 40 in july and 20 in May).    Now my PR qty in May 90 because for July lead month 2 so it is may , for June lead time 2 but we do not have April so show in May.    similar for May lead time 2 but dont have March should show in May itself. So total my PR 90 ... How i can calculated this any idea. here is my report layout. Thanks, Hari.

[Former Member]

Hi Leod,

It is actually MRP report, User key in only MRP Upload date and Plant.

I am getting all information like lead time in months, demand qty etc from my customized data sources into MP.

I have to calculate PR(purchase requisition) qty and show in respective month based on lead time month.

Example:

1. I have lead month 2 , Now it is May my demand qty ( 30 in June , 40 in july and 20 in May).    Now my PR qty in May 90 because for July lead month 2 so it is may , for June lead time 2 but we do not have April so show in May.  

similar for May lead time 2 but dont have March should show in May itself. So total my PR 90 ... How i can calculated this any idea. here is my report layout.

Thanks,

Hari.

[Loed]

Hi Hari,

You didn't answer my questions..

What I mean is, if you run your query, is there an INPUT VARIABLE where user needs to enter values? Like calmonth or lead time, etc..

Also, what are the object in your ROWS and COLUMNS? Can you show your query designer?

Because we might achieve this using CMOD but I need to know the answers on the questions above..

Regards,

Loed

[Former Member]

Hi Loed,

As I mentioned User key in MRP upload date only this is input variable.

lead time I am the data I am getting from my infocube to MP.

Please look at this:

Thanks,

Hari

[Loed]

Hi Hari,

So 0DATE is mapped to your CALMONTH and CALWEEK in the transformation level?

What's the purpose of your 0CALWEEK in the COLUMNS part? I thought you will only get the values of DEMAND QTY based in months?

If you enter May 15, 2015 what are the values that will appear in your CALMONTH? Is it YTD? So January to May 2015?

How will you know the current lead time that you will be using in calculation? For example right now in May, how will you know the lead time? Do you have lead time value every month?

Regards,

Loed

[Former Member]

Hi Loed,

Here is my screen for the May month result in DEV mostly no data . Regarding lead time all the way same for the material. Because every month we run MRP in ECC for all plant.

Thanks ,

Hari

[Loed]

Hi Hari,

Sorry, I still can't understand your scenario..

In your screen above, where is  your PR QTY? You only have problem for this KF right?

Is your LEAD TIME fixed for 3? Or is it changing every month?

If it is fixed, we can use offset to get the PR QTY for three (3) months in advance based on your input date..

Regards,

Loed

[Former Member]

Hi Loed, Yes in my query PR qty not there because I have to build the formula to get PR qty based on Planned delivery time and demand forecast. Here Planed delivery time getting from MARC-PLIFZ which is in day and then I am calculating into month (days/30)assuming 30days as month. The calculation I am doing in my transformation using formula. Now I want to formula to build PR qty from demand forecast. I mentioned if lead time 2 all the ways 2month ahead should show in PR qty from demand forecast. Hope you understand now. Thanks, Hari

[Loed]

Hi Hari,

So lead time ALWAYS have a value of 2? It will not change?

Regarding my question above, is 0DATE mapped to your 0CALMONTH in the transformation level?

Regards,

Loed

[Former Member]

Hi Loed, As I mentioned Lead time is not fixed, I am getting from MARC. yes 0date mapped to 0calmonth.

Thanks,

Hari

[Former Member]

Hi All, Now I have to calculate PR Qty how to add logic in customer exit.. Any way I can use ABAP or VB how? IF ABAP I can do if you give the procedure, IF VB I do not know how to write logic and add logic. Thanks, Hari

[Loed]

Hi Hari,

The data which will be shown is based on your 0DATE? So your 0DATE is a range of dates not just a single date as per your screenshot?

For example, to be able to achieve this user will enter Jan 1, 2015 to April 30, 2015..Is this right?

                                                                    months

                   lead time                         1          2        3         4

                           2          demand       40       30      20      20

                                        required      90       20

Also, PLANNED DELIVERY TIME is the one you called LEAD TIME? Are they the same?

Regards,

Loed

[Former Member]

Hi Loed, User can enter only MRP Loading Date. Yes, Planned delivery time is lead time. Thanks, Hari

[Former Member]

Hi Experts, Kindly help... . Please update me asap. Thanks, Hari

[Loed]

Hi Hari,

What do you mean by MRP Loading date? How are you gonna show the different values of your months?

For example, in the scenario you have posted:

                                                                    months

                   lead time                         1          2        3         4

                           2          demand       40       30      20      20

                                        required      90       20

How are you going to show these four (4) months? Assuming that today is April 19, 2015..What will the user enter in the MRP Loading Date to achieve the result above? Is it single date only (like the loading date today which is April 19, 2015) or range of dates (like January 1, 2015 to April 19, 2015)?

Regards,

Loed

[Former Member]

Hi Loed,,  colander week is from customer exit ...any formula we can achieve...

[Loed]

Hi Hari,

What does your customer exit variable 0I_WEKIN do?

What do you mean by MRP Loading Date? So this is just a single date entry? Is this related to 0I_WEKIN variable? How can you show the four (4) months data of the sample above? What do you need to enter in MRP Loading Date?

Regards,

Loed

[Former Member]

Hi Loed,

Here is the code for date in exit.

\

Thanks,

Hari

[Loed]

Hi Hari,

I think you posted the wrong CMOD code?

Because based on your FILTER pane, you are using 0I_WEKIN which I think is a SAP exit..But you posted ZKEY_DATE as the variable below..

Did you understand my question?

Regards,

Loed

[Former Member]

Hi Loed,

Sorry  thought key date but it is coming form SAP exit.

,

Thanks and Regards,

Harikiran

[Former Member]

Hi Loed, Can you share some formula how I can code in Customer exit this value. I have now very less time by today itself I need to update this formula. Thanks, Hari

[Loed]

Hi Hari,

You still didn't answer my question how you can achieve these four (4) months by using the MRP Loading Date only..What are you going to enter in the MRP Loading Date to achieve sample result below? Are you going to use only 1 date or range of dates in your MRP Loading date variable?

                                                                    months

                   lead time                         1          2        3         4

                           2          demand       40       30      20      20

                                        required      90       20

Regards,

Loed

[Loed]

Hi Hari,

Sorry I can't think of any solution just by using query designer....

If you only have FEW values of lead time, the only way I can think is to get this done in a workbook so as we can use the functionality of EXCEL..

I attached SAMPLE workbook (extract the file, change the extension name from .TXT to .ZIP, extract again the file, then open the file in EXCEL), I'm just sharing it for you to have an idea but I think this is NOT the best solution..

Regards,

Loed

Workaround.txt.zip

[Former Member]

Hi Loed,

I did not see any attachment.

is this we can achieve by using customer exit. or Start routine.

Thanks,

Harikiran.

[Loed]

Hi Hari,

There is an attachment below my name (above the LIKE button) - 9.5K..

I don't know if we can achieve this by using customer exit since we need to get the LEAD TIME for every row to be able to know how many months will be added..

Let's just wait for others' ideas or you may try to check the workbook I have attached..

Regards,

Loed

[Former Member]

Hi Loed,

I got formula but to implement this I need lead time and , demand period values, demand quantity values in Customer exit. I am getting all in my BEx and Transformation. Thought to write routine in Transformation but lead time in one transformation and demand period and demand quantity one transformation.

My formula :

if (Demand Period - lead time ) LE Current month.

   in Current month period

   PR quantity = demand quantity for that demand period (next record also fall in this condition need to sum with previous)

else if (demand period-Lead time ) GT Current month.

      what ever period comes from formula (Demand period-Lead time ) in that period

    PR quantity =  demand quantity of Demand period.

endif.

Is there anyway I can get the BEx key figures in Customer exit. If not I will change my data source into single data source in ECC and write the formula in routine or start routine.

Thanks ,

Hari

[Loed]

Hi Hari,

Are the fields you have mentioned present in the query designer?

What are those fields in your screenshot?

Regards,

Loed

[Former Member]

Hi Loed ,

I managed to write in End routine.

now I want my column show only present month to 8 in the excel rest of the month I do not want to see, How I can achieve.

Thanks and Regards,

Hari

[Loed]

Hi Hari,

What do you mean by present month to 8? I was asking you before how are you going to show the four (4) just by entering MRP Loading Date? Are you going to enter single date or range? We can use OFFSET for your requirement but I need to know how are you going to show the sample four (4) months..

Regards,

Loed

[Former Member]

Hi Loed,

I want based on MRP Loading date month to 8 or 10 month only display in the report.

for those months only I want to see calendar week.

for Three month I showed in excel as below: like this  05.2015 to 01.2016 (8month) how I can achieve this dynamically.

Thanks,

Hari

[Former Member]

Hi Loed,

I did myself my logic at endroutine. but I facing issue that whenever there is no values in any month on Demand it is not showing calmonth cause I am mapping with demand date to all calmonth, cal week and cal day. Now how I can show my Purchase requisition in calmonth not shows in report.

example 5,6,7 and 8 month in this 5,7,8 only have demand and my lead month is 2 so my purchase requisition 8th month 8-2 means 6th month but 6th month do not have calmonth.

then data how to populate.

Thanks ,

Hari