YTD Based on Fiscal Year Period without cmod code

Follow RSS feed Like

1 Like 1,592 View 85 Comments

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).

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

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

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

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:

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.

Alert Moderator

85 Comments

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

Former Member

August 22, 2013 at 10:09 am

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

Thanks for sharing and making Anshu.

Raman
- Like(0)
- Anshu Lilhori Post author
  
  August 22, 2013 at 10:11 am
  
  Thanks a lot Raman
  
  Regards,
  
  AL
  - Like(0)
  - prabhith prabhakaran
    
    August 22, 2013 at 6:14 pm
    
    Good one ,, Anshu…..
    Like(0)
    - Anshu Lilhori Post author
      
      August 22, 2013 at 6:17 pm
      
      Thanks prabhith.
      
      Regards,
      
      AL
      
      Like(0)
Umashankar Poojar

August 22, 2013 at 12:22 pm

simple and useful document, thanks for sharing.

cheers!

Umashankar
- Like(0)
- Anshu Lilhori Post author
  
  August 22, 2013 at 12:23 pm
  
  Thanks Umashankar.
  
  Regards,
  
  AL
  - Like(0)
Former Member

August 22, 2013 at 5:41 pm

Thanks Anshu for sharing,

although very basic but good to be documented.
- Like(0)
- Anshu Lilhori Post author
  
  August 22, 2013 at 5:45 pm
  
  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
  - Like(0)
Martin Grob

August 22, 2013 at 6:02 pm

interesting concept!

Martin
- Like(0)
Former Member

August 22, 2013 at 6:41 pm

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
- Like(0)
- Anshu Lilhori Post author
  
  August 23, 2013 at 4:06 am
  
  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
  - Like(0)
Former Member

August 23, 2013 at 9:36 am

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
- Like(0)
Former Member

August 24, 2013 at 5:19 am

Anshu ….Excellent.
- Like(0)
- Anshu Lilhori Post author
  
  August 24, 2013 at 6:27 am
  
  Thanks a lot Vijay…
  
  Regards,
  
  AL
  - Like(0)
Former Member

August 28, 2013 at 12:26 pm

Nice Document Anshu 🙂 Expecting more this kind of docs from you !!
- Like(0)
- Anshu Lilhori Post author
  
  August 28, 2013 at 12:30 pm
  
  Thanks Venu..surely will share more docs soon.
  
  Regards,
  
  AL
  - Like(0)
SRIKANTH GARBHAM

August 28, 2013 at 12:57 pm

Simple and very useful document.

Thanks

Srikanth.
- Like(0)
- Anshu Lilhori Post author
  
  August 28, 2013 at 1:00 pm
  
  Thanks Srikanth.
  
  Regards,
  
  AL
  - Like(0)
Former Member

August 29, 2013 at 5:06 pm

Achieving great things with simple thinking.
- Like(0)
- Anshu Lilhori Post author
  
  August 29, 2013 at 5:34 pm
  
  Thnx for the feedback…
  - Like(0)
Former Member

August 29, 2013 at 5:37 pm

Very Useful Document…Many Thanks.

Best Regards,

Naresh K.
- Like(0)
- Anshu Lilhori Post author
  
  August 29, 2013 at 5:53 pm
  
  Thnx Naresh.
  
  Regards,
  
  AL
  - Like(0)
Pavan Kumar K R

September 2, 2013 at 9:35 am

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
- Like(0)
- Anshu Lilhori Post author
  
  September 2, 2013 at 9:38 am
  
  Thanks Pavan for your motivational comments.
  
  Much appreciated.
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 2, 2013 at 12:28 pm

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.
- Like(0)
- Anshu Lilhori Post author
  
  September 2, 2013 at 12:32 pm
  
  Thanks Kamal for your feedback.
  
  Yes we can play around with different time characteristic as per our need.
  
  Regards,
  
  AL
  - Like(0)
Krishna Chaitanya

September 2, 2013 at 12:34 pm

Really helpful..Informative..Thanks. 🙂 Regards, Krishna Chaitanya.
- Like(0)
- Anshu Lilhori Post author
  
  September 2, 2013 at 12:35 pm
  
  Thnx Krishna..
  
  Regards,
  AL
  - Like(0)
Prashanth konduru

September 11, 2013 at 9:16 am

Good Job Anshu … Simple n short n Useful one.

Thanks for sharing…

Regards

KP
- Like(0)
- Anshu Lilhori Post author
  
  September 11, 2013 at 9:24 am
  
  Thanks KP for your feedback.
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 12, 2013 at 12:30 pm

Hello Anshu

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

Regards,

SAtendra Singh Sikarwar
- Like(0)
- Anshu Lilhori Post author
  
  September 12, 2013 at 12:40 pm
  
  HI Satendra,
  
  Thanks for your valuable feedback..Really appreciated.
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 19, 2013 at 6:03 am

Hi Anshu,

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

Thanks for sharing.
- Like(0)
- Anshu Lilhori Post author
  
  September 19, 2013 at 6:19 am
  
  Thanks KD for your comments..Much appreciated.
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 19, 2013 at 9:52 am

Hi Anshu,

Nice document with real-time scenario.

Regards,

Venkatesh
- Like(0)
- Anshu Lilhori Post author
  
  September 19, 2013 at 9:54 am
  
  Thanks venkastesh..for your comments.
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 19, 2013 at 11:44 am

Hi anshu,

Excellent.…Simple and useful document.

Thanks lot for sharing your ideas… 🙂

regards

Sandeep
- Like(0)
- Anshu Lilhori Post author
  
  September 19, 2013 at 12:11 pm
  
  Thanks sandeep for your cheerful comments.
  
  Regards,
  
  AL
  - Like(0)
Sanil Bhandari

September 19, 2013 at 12:03 pm

Thanks really useful

Regards

Sanil
- Like(0)
- Anshu Lilhori Post author
  
  September 19, 2013 at 12:12 pm
  
  Thanks Sanil..Much appreicated your comments.
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 19, 2013 at 12:03 pm

Nice Document anshu,

🙂

Grate work 🙂

ragards,

SM
- Like(0)
- Anshu Lilhori Post author
  
  September 19, 2013 at 12:14 pm
  
  Thanks satendra..for your wonderful comments.really elated. 🙂
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 19, 2013 at 1:53 pm

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
- Like(0)
- Anshu Lilhori Post author
  
  September 19, 2013 at 3:02 pm
  
  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
  - Like(0)
  - Former Member
    
    September 19, 2013 at 3:51 pm
    
    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
    Like(0)
    - Anshu Lilhori Post author
      
      September 19, 2013 at 3:54 pm
      
      No issues Mahesh..You are always welcome.. 🙂
      
      Like(0)
Former Member

September 20, 2013 at 5:26 am

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

Best Regards,

Naresh K.
- Like(0)
- Anshu Lilhori Post author
  
  September 20, 2013 at 5:29 am
  
  Thanks Naresh..Much appreciated..Really elated.. 🙂
  
  Regards,
  
  AL
  - Like(0)
Former Member

September 20, 2013 at 9:16 am

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 🙂
- Like(0)
- Anshu Lilhori Post author
  
  September 20, 2013 at 9:26 am
  
  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
  - Like(0)
  - Former Member
    
    September 20, 2013 at 9:37 am
    
    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 😎
    Like(0)
    - Anshu Lilhori Post author
      
      September 20, 2013 at 9:40 am
      
      Yes sure we can do that i mentioned also somewhere in the document that same can be applicable for other time dimension as well.
      
      Like(0)
Former Member

September 20, 2013 at 4:47 pm

Good approach..!
- Like(0)
- Anshu Lilhori Post author
  
  September 20, 2013 at 4:49 pm
  
  Thanks for the feedback…
  
  Regards,
  AL
  - Like(0)
Former Member

September 29, 2013 at 1:32 pm

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

Fanntastic job 🙂 🙂
- Like(0)
- Anshu Lilhori Post author
  
  September 29, 2013 at 2:22 pm
  
  Thanks suman..Good to get a response from the BEx topic leader 🙂 ..
  
  Regards,
  
  AL
  - Like(0)
  - Former Member
    
    September 29, 2013 at 2:57 pm
    
    😆 😆 Anshu. You too are a Bex Topic Leader 🙂
    Like(0)
Former Member

September 30, 2013 at 5:07 am

Very interesting and helpful presentation.

Thanks for Sharing,

Anil.
- Like(0)
- Anshu Lilhori Post author
  
  September 30, 2013 at 5:26 am
  
  Thanks Anil..Hopefully this might come handy for your future requirements related to YTD.
  
  Regards,
  
  AL
  - Like(0)
Former Member

October 1, 2013 at 6:35 am

Nice Document and really very helpful… 🙂
- Like(0)
- Anshu Lilhori Post author
  
  October 1, 2013 at 6:40 am
  
  Thanks Mithun for your valuable feedback..
  
  Regards,
  
  AL
  - Like(0)
cherry goud

October 3, 2013 at 5:21 am

superb and thanks for sharing it.

Regards,

Cherry.
- Like(0)
- Anshu Lilhori Post author
  
  October 3, 2013 at 5:23 am
  
  Thanks Cherry for your valuable comments.
  
  Much appreciated.
  
  Regards,
  
  AL
  - Like(0)
cherry goud

October 3, 2013 at 5:25 am

You are welcome anshu.

Regards,

Cherry.
- Like(0)
Former Member

October 7, 2013 at 1:05 pm

Very useful …. Thanks for sharing Anshu Lilhori.

Regards

Subbrao M
- Like(0)
- Anshu Lilhori Post author
  
  October 7, 2013 at 3:14 pm
  
  Thanks Subbarao..
  
  Really appreciate your comments.
  
  Regards,
  
  AL
  - Like(0)
Former Member

October 7, 2013 at 9:30 pm

Nice document. Thanks again..
- Like(0)
- Anshu Lilhori Post author
  
  October 8, 2013 at 2:28 pm
  
  Thanks Chandra for your feedback.
  
  Regards,
  
  AL
  - Like(0)
Former Member

October 15, 2013 at 10:43 am

Hi Anshu,

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

Thanks for sharing.

BR

Aakash G
- Like(0)
- Anshu Lilhori Post author
  
  October 15, 2013 at 2:51 pm
  
  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
  - Like(0)
Former Member

December 27, 2013 at 10:02 am

its good. Better approach. Thanks for sharing.
- Like(0)
Anshu Lilhori Post author

December 27, 2013 at 10:14 am

Thanks for your feedback..

Regards,

AL
- Like(0)
Phani KV

January 8, 2014 at 4:14 am

Hi Anshu,

very good document with out CMOD 🙂 🙂 🙂

Thanks,

Phani.
- Like(0)
- Anshu Lilhori Post author
  
  January 8, 2014 at 9:05 am
  
  Thanks Phani for your feedback on the same.
  
  Regards,
  
  AL
  - Like(0)
Antony Jerald J

January 12, 2014 at 2:53 pm

Really Helpful Anshu Lilhori 🙂

Regards,

Antony Jerald.
- Like(0)
- Anshu Lilhori Post author
  
  January 13, 2014 at 8:44 am
  
  I am glad that its helpful for you.Thanks indeed.
  
  Regards,
  
  AL
  - Like(0)
Former Member

April 7, 2014 at 6:48 pm

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
- Like(0)
Former Member

July 8, 2014 at 9:01 am

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
- Like(0)
- Anshu Lilhori Post author
  
  July 8, 2014 at 4:26 pm
  
  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
  - Like(0)
  - Former Member
    
    July 9, 2014 at 5:06 am
    
    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.
    Like(0)
Former Member

October 13, 2014 at 6:30 am

Hi,

Realy Very Nice Solution to achieve YTD without coding.

Regards

Jagan
- Like(0)
Fahad Qureshi

June 24, 2015 at 3:45 pm

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
- Like(0)
Former Member

March 17, 2016 at 5:00 pm

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…!!!!???
- Like(0)
- Kiran K
  
  June 8, 2017 at 5:40 pm
  
  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 ).
  - Like(0)
Former Member

February 27, 2018 at 12:08 pm

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
- Like(0)