Skip to Content
Actions

YTD Based on Fiscal Year Period without cmod code

August 22, 2013 | 1,155 Views |
 sap business explorer (bex)
Follow

Scenario


Sometimes we may get a requirement to get the YTD on ficsal year period based on user input.The first thing we think of achieving it is by coding.Here i will give step by step walk through to get the same without coding.

Lets take an example we need to get the sales document count as YTD.

Step 1. Create a selection or RKF on Document count keyfigure

Drag and drop 0FISCPER in selection.Right click on 0FISCPER—Restrict–Show–Value ranges—Less than or equal to–Choose the variable 0P_FPER (User input variable).

2.JPG

1.JPG

Step 2: Drag and drop fiscal year in the same selection.

            Restrcit–Variable–Create new variable.Refer the screenshot for details

3.JPG

Hit the replacement path tab and give the details as mentioned in below screenshot

4.JPG

Note: We chose replace with Key so in offset we gave start length as 2 and length 4.Basically we are trying to get the year from the user input variable based on fiscal year period.

As fiscal year is compounded with fiscal year variant and it is stored in this fashion K42003. So to get the year we gave the lengths as above.

Now the final Selection or RKF looks look like this:

5.JPG

By following the above steps we can get the YTD on fiscal year period based on user input value.

Hope its useful and you guyz will like it.

All suggestions are welcome.

To report this post you need to login first.

85 Comments

You must be Logged on to comment or reply to a post.

    1. Anshu Lilhori Post author

      Thanks sushant..Definitely we will try to explore the options where we can avoid the coding and make it possible with standard features of BEx.

      Regards,

      AL

      (0) 
  6. Manna Das

    I like code free designing in BW and this doc give me one more reason to design YTD reports in Bex. Thanks Anshu for sharing this.

    Kind Regards

    Manna Das

    (0) 
  12. Pavan Kumar K R

    Anshu its Wow!

    A Thought provoking document. Most of the times we thought of achieving such requirements with the help of code. But this doc made me to think different and make most out the Tool’s functionality.

    Keep Bloging !!

    — KRPK

    (0) 
  13. Kamal Mehta

    Wonderful document.

    Also indicate we can also try achieving some of the other stuff like MTD  without writing code. The only exception would be user entering dates instead of fiscal year period.

    Thanks

    Bookmarked.

    (0) 
  22. mahesh bondula

    Hi Anshu,

    Excellent document , Thanks for sharing.

    I have few queries on the said issue.

    (1)What is the main difference between your logic & previous CMOD Logic? I mean do ur logic got any limitations or it is 100% replacement of CMOD?

    (2) In CMOD whenever user enters YTD date, it brings all data from Infocube, But in ur case Specificially u need to drag (sales doc count) into selection. Say if we have 100 sales related doc fields then we have to drag 100 fields. Is it so? 

    (3) Normally Variable value is maintained in “E_T_RANGE” & User input value is maintained in ‘I_T_VAR_RANGE’. But in your case how u are maintaining this values?

    Could u be pls more eleborative on my questiions.

    Thanks in Advance.

    Regards,

    Mahesh

    (0) 
    1. Anshu Lilhori Post author

      Hi Mahesh,

      I would have appreciated if you had asked the queries in a new thread referring my document.

      (1)What is the main difference between your logic & previous CMOD Logic? I mean do ur logic got any limitations or it is 100% replacement of CMOD?

      In my approach you do not need to write any code.If your requirement is to calculate simple YTD based on user input without any special periods then yes it can replace cmod code.

      (2) In CMOD whenever user enters YTD date, it brings all data from Infocube, But in ur case Specificially u need to drag (sales doc count) into selection. Say if we have 100 sales related doc fields then we have to drag 100 fields. Is it so?

      The approach will be same in cmod also.Mostly the reporting requirement would be to calculate current YTD sales and Previous YTD sales so there you will make the selections like i did and restrict the data rather than globally restricting in char restriction.

      (3) Normally Variable value is maintained in “E_T_RANGE” & User input value is maintained in ‘I_T_VAR_RANGE’. But in your case how u are maintaining this values?

      If you read my document thoroughly then you will understand that i have given one user input variable 0P_FPER which holds the user input value.Based on this value only YTD will be calculated.

      I hope i have answered your queries to your expectations.

      Still i would suggest to give a try to this approach and any issue let me know.

      I am always there to help you out.

      Regards,

      AL

      (0) 
      1. mahesh bondula

        HI Anshu,

        I did not thought of creating new thread, as the topic is related to same issue.

        I am extremely sorry from my end, I was just clarifying my doubts.

        Thanks for your support & i feel I need to learn many things from u :-).

        Regards,

        Mahesh

        (0) 
  24. Kumar S

    Hi Anshu…Nice information. We can jump for any requirement without code. Thank you.

    Can we expect based on calday etc also as addition to this 🙂

    (0) 
    1. Anshu Lilhori Post author

      Hi,

      Thanks for your valuable feedback..based on the requirement we can check what can be achieved without code..

      Calday–if you share the requirement then i may think and see the possibilities of trying the same.

      Regards,

      AL

      (0) 
      1. Kumar S

        wow….quick reply. I am asking same YTD requirement based on calday even. I think same with some offset change.

        Then YTD Based on Fiscal Year Period and CALDAY without cmod code 😎

        (0) 
    1. Anshu Lilhori Post author

      Hi Aakash,

      Thanks for your cheerful comments.Yes You can avoid the coding following this approach provided the requirement is straight forward without any special considerations and logic.

      Regards,

      AL

      (0) 
  38. Daniel Ray

    awesome explanation and one of its kind, as mostly this will be handled through code and I have learnt a new way to handle YTD without code. This can be applicable to MTD, PYTD, PMTD all *TD 😀 ….

    For those who missed, 0P_FPER is a user entry variable, which can be replaced with ZVAR created on 0FISCPER to fetch the 0FISCPER value from the user 😀 I got confused so adding these comments , if any one come cross it.

    Good job AL. I am really lucky to go through this. Now I am a YTD king 😀 😀 😀 .

    Cheers

    DR

    (0) 
  39. Farooq Hussain

    Hi Anshu, thanks for such informative post. I am trying to do YTD for fin reports. Actual/Budget comparisons. I get MTD figures in my current report. See cols of the report below. I Created Selections as Actual and Budget and followed the steps given in your doc. but the the figures do not cumulate to the fiscal year / period entered, they remain MTD.

    Actual Budget Previous Yr Var ACT vs BUD

    Am I missing something… appreciate any help..

    Thanks

    MF

    (0) 
    1. Anshu Lilhori Post author

      MF,You need to check if there is any variable on fiscal year period or fiscal year kept in global filter as this will restrict your data.All filters should be local as described in document.

      Also to check in step 1 you chose less than or equal to in value ranges.

      You can also share the screenshot of the selections .

      Regards,

      AL

      (0) 
      1. Farooq Hussain

        AL, thanks for the quick response, I checked the settings, look ok. In Bex there are no Global filters however the data imported into BW is for year 2012 only (set in InfoPackage). I have removed all chars and using only the Selection YTD to make sure it works. Pls see the screen shots. Thanks.

        YTD1.png

        YTD2.png

        YTD3.png

        (0) 
  41. Fahad Qureshi

    Awesome solution Anshu

    I have taken this and modified it on the standard payroll cube to get the YTD values.

    However I am still getting last year values as well

    I don’t have 0FISCYEAR only 0CALYEAR

    To get the period values i used 0FPER (For Period) format is yyyypp e.g. 201501

    (0) 
  42. Suresh Kotakonda

    What i understood from this section is user has to give the input of Fiscal Period (Ex: 001.2015). As per the above guide the custom variable which is used on Fiscal Year with processing type replacement path will take the year from user input (Example if the user input is 001.2015, it will be considered the year 2015) and pass this value in the Fiscal year.

    So obviously we will get the complete data for that particular Fiscal year 2015 that is YTD. This is excellent idea, but i dont understand why we are doing such a lengthy procedure to bring this. Instead of giving Fiscal period input, why cant the user directly input the fiscal year (By creating the input variable on Fiscal Year). This also will give us the YTD values.

    Can some one please enlighten why we should give fiscal period input and convert the input into the fiscal year and pass it to fiscal year….???!!!! Does my thought make sense…!!!!???

    (0) 
    1. Kiran K

      I think this case will be useful in the below scenarios .

      Ex:currently you are in the month jun 2017, if user runs report with Apr 2017 then u will get data only from jan 2017 to Apr 2017.

       

      But if u just pass year alone it will give jan to jun 2017 (till date ).

      (0) 
  43. Vishal Kumar

     

    Very useful Doc Anshu..

    If you can help me out with below requirement pls:

    I need to calculate YTD hours for the Date range given by the user(HR data)

    How can I accomplish this without CMOD (like by Replacement Path)

    Really Appreciate your help anticipating in Advance..:)

    Any prompt help is appreciated guys..

    VK

    (0) 

Leave a Reply