Skip to Content
Author's profile photo Anshu Lilhori

YTD Based on Fiscal Year Period without cmod code

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.

Assigned tags

      85 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo CH Raman
      CH Raman

      Really helpful and useful article. Nice presentation with real scenario..

      Thanks for sharing and making Anshu.

      Raman

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks a lot Raman

      Regards,

      AL

      Author's profile photo prabhith prabhakaran
      prabhith prabhakaran

      Good one ,, Anshu.....

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks prabhith.

      Regards,

      AL

      Author's profile photo Umashankar Poojar
      Umashankar Poojar

      simple and useful document, thanks for sharing.

      cheers!

      Umashankar

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Umashankar.

      Regards,

      AL

      Author's profile photo vinod bokade
      vinod bokade

      Thanks Anshu for sharing,

      although very basic but good to be documented.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx vinod..Most of the people go for code to get the same so thought of sharing this simple method without any code.

      Regards,
      AL

      Author's profile photo Martin Grob
      Martin Grob

      interesting concept!

      Martin

      Author's profile photo Former Member
      Former Member

      Its very useful document and waiting for many documents similar like due to which life of BW Consultant become little bit code free.

      Regards,

      Sushant

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Vijay Chandra.R
      Vijay Chandra.R

      Anshu ....Excellent.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks a lot Vijay...

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice Document Anshu 🙂 Expecting more this kind of docs from you !!

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Venu..surely will share more docs soon.

      Regards,

      AL

      Author's profile photo SRIKANTH GARBHAM
      SRIKANTH GARBHAM

      Simple and very useful document.

      Thanks

      Srikanth.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Srikanth.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Achieving great things with simple thinking.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx for the feedback...

      Author's profile photo Former Member
      Former Member

      Very Useful Document...Many Thanks.

      Best Regards,

      Naresh K.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx Naresh.

      Regards,

      AL

      Author's profile photo Pavan Kumar K R
      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

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Pavan for your motivational comments.

      Much appreciated.

      Regards,

      AL

      Author's profile photo Kamal Mehta
      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.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Kamal for your feedback.

      Yes we can play around with different time characteristic as per our need.

      Regards,

      AL

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Really helpful..Informative..Thanks.  🙂  Regards, Krishna Chaitanya.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thnx Krishna..

      Regards,
      AL

      Author's profile photo Prashanth konduru
      Prashanth konduru

      Good Job Anshu ... Simple n short n Useful one.

      Thanks for sharing...

      Regards

      KP

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks KP for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hello Anshu

      Its very useful n step by step procedure to get the YTD on ficsal year period .

      Regards,

      SAtendra Singh Sikarwar

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      HI Satendra,

      Thanks for your valuable feedback..Really appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Nice approach and a good BI consultant should try to avoid code stuff...

      Thanks for sharing.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks KD for your comments..Much appreciated.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Nice document with real-time scenario.

      Regards,

      Venkatesh

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks venkastesh..for your comments.

      Regards,

      AL

      Author's profile photo Sandeep a
      Sandeep a

      Hi anshu,

      Excellent....Simple and useful document.

      Thanks lot for sharing your ideas...  🙂

      regards

      Sandeep

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks sandeep for your cheerful comments.

      Regards,

      AL

      Author's profile photo Sanil Bhandari
      Sanil Bhandari

      Thanks really useful

      Regards

      Sanil

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Sanil..Much appreicated your comments.

      Regards,

      AL

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice Document anshu,

      🙂

      Grate work 🙂

      ragards,

      SM

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks satendra..for your wonderful comments.really elated. 🙂

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      No issues Mahesh..You are always welcome.. 🙂

      Author's profile photo Former Member
      Former Member

      Hearty Congratulations Anshu Lilhori SCN Topic Leader 2012-2013. Kudos on your outstanding accomplishment.

      Best Regards,

      Naresh K.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Naresh..Much appreciated..Really elated.. 🙂

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      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 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog 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

      Author's profile photo Former Member
      Former Member

      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 😎

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Yes sure we can do that i mentioned also somewhere in the document that same can be applicable for other time dimension as well.

      Author's profile photo Former Member
      Former Member

      Good approach..!

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for the feedback...

      Regards,
      AL

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      I like to do all trend analysis by this kind of tricks only..I hate CMOD.

      Fanntastic job 🙂 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks suman..Good to get a response from the BEx topic leader 🙂 ..

      Regards,

      AL

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      😆 😆 Anshu. You too are a Bex Topic Leader 🙂

      Author's profile photo Former Member
      Former Member

      Very interesting and helpful presentation.

      Thanks for Sharing,

      Anil.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Anil..Hopefully this might come handy for your future requirements related to YTD.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice Document and really very helpful... 🙂

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Mithun for your valuable feedback..

      Regards,

      AL

      Author's profile photo cherry goud
      cherry goud

      superb and thanks for sharing it.

      Regards,

      Cherry.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Cherry for your valuable comments.

      Much appreciated.

      Regards,

      AL

      Author's profile photo cherry goud
      cherry goud

      You are welcome anshu.

      Regards,

      Cherry.

      Author's profile photo Former Member
      Former Member

      Very useful .... Thanks for sharing Anshu Lilhori.

      Regards

      Subbrao M

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Subbarao..

      Really appreciate your comments.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Nice document. Thanks again..

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Chandra for your feedback.

      Regards,

      AL

      Author's profile photo Former Member
      Former Member

      Hi Anshu,

      Very good document , very useful. Nice way to escape from coding 😉 😉 ...

      Thanks for sharing.

      BR

      Aakash G

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog 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

      Author's profile photo Former Member
      Former Member

      its good. Better approach. Thanks for sharing.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks for your feedback..

      Regards,

      AL

      Author's profile photo Phani KV
      Phani KV

      Hi Anshu,

      very good document with out CMOD 🙂 🙂 🙂

      Thanks,

      Phani.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      Thanks Phani for your feedback on the same.

      Regards,

      AL

      Author's profile photo Antony Jerald J
      Antony Jerald J

      Really Helpful Anshu Lilhori 🙂

      Regards,

      Antony Jerald.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog Post Author

      I am glad that its helpful for you.Thanks indeed.

      Regards,

      AL

      Author's profile photo Daniel Ray
      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Anshu Lilhori
      Anshu Lilhori
      Blog 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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi,

      Realy Very Nice Solution to achieve YTD without coding.

      Regards

      Jagan

      Author's profile photo Fahad Qureshi
      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

      Author's profile photo Former Member
      Former Member

      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...!!!!???

      Author's profile photo Kiran K
      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 ).

      Author's profile photo Former Member
      Former Member

       

      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