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.
Really helpful and useful article. Nice presentation with real scenario..
Thanks for sharing and making Anshu.
Raman
Thanks a lot Raman
Regards,
AL
Good one ,, Anshu…..
Thanks prabhith.
Regards,
AL
simple and useful document, thanks for sharing.
cheers!
Umashankar
Thanks Umashankar.
Regards,
AL
Thanks Anshu for sharing,
although very basic but good to be documented.
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
interesting concept!
Martin
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
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
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
Anshu ….Excellent.
Thanks a lot Vijay…
Regards,
AL
Nice Document Anshu 🙂 Expecting more this kind of docs from you !!
Thanks Venu..surely will share more docs soon.
Regards,
AL
Simple and very useful document.
Thanks
Srikanth.
Thanks Srikanth.
Regards,
AL
Achieving great things with simple thinking.
Thnx for the feedback…
Very Useful Document…Many Thanks.
Best Regards,
Naresh K.
Thnx Naresh.
Regards,
AL
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
Thanks Pavan for your motivational comments.
Much appreciated.
Regards,
AL
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.
Thanks Kamal for your feedback.
Yes we can play around with different time characteristic as per our need.
Regards,
AL
Really helpful..Informative..Thanks. 🙂 Regards, Krishna Chaitanya.
Thnx Krishna..
Regards,
AL
Good Job Anshu … Simple n short n Useful one.
Thanks for sharing…
Regards
KP
Thanks KP for your feedback.
Regards,
AL
Hello Anshu
Its very useful n step by step procedure to get the YTD on ficsal year period .
Regards,
SAtendra Singh Sikarwar
HI Satendra,
Thanks for your valuable feedback..Really appreciated.
Regards,
AL
Hi Anshu,
Nice approach and a good BI consultant should try to avoid code stuff…
Thanks for sharing.
Thanks KD for your comments..Much appreciated.
Regards,
AL
Hi Anshu,
Nice document with real-time scenario.
Regards,
Venkatesh
Thanks venkastesh..for your comments.
Regards,
AL
Hi anshu,
Excellent.…Simple and useful document.
Thanks lot for sharing your ideas… 🙂
regards
Sandeep
Thanks sandeep for your cheerful comments.
Regards,
AL
Thanks really useful
Regards
Sanil
Thanks Sanil..Much appreicated your comments.
Regards,
AL
Nice Document anshu,
🙂
Grate work 🙂
ragards,
SM
Thanks satendra..for your wonderful comments.really elated. 🙂
Regards,
AL
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
Hi Mahesh,
I would have appreciated if you had asked the queries in a new thread referring my document.
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.
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.
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
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
No issues Mahesh..You are always welcome.. 🙂
Hearty Congratulations Anshu Lilhori SCN Topic Leader 2012-2013. Kudos on your outstanding accomplishment.
Best Regards,
Naresh K.
Thanks Naresh..Much appreciated..Really elated.. 🙂
Regards,
AL
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 🙂
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
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 😎
Yes sure we can do that i mentioned also somewhere in the document that same can be applicable for other time dimension as well.
Good approach..!
Thanks for the feedback…
Regards,
AL
I like to do all trend analysis by this kind of tricks only..I hate CMOD.
Fanntastic job 🙂 🙂
Thanks suman..Good to get a response from the BEx topic leader 🙂 ..
Regards,
AL
😆 😆 Anshu. You too are a Bex Topic Leader 🙂
Very interesting and helpful presentation.
Thanks for Sharing,
Anil.
Thanks Anil..Hopefully this might come handy for your future requirements related to YTD.
Regards,
AL
Nice Document and really very helpful… 🙂
Thanks Mithun for your valuable feedback..
Regards,
AL
superb and thanks for sharing it.
Regards,
Cherry.
Thanks Cherry for your valuable comments.
Much appreciated.
Regards,
AL
You are welcome anshu.
Regards,
Cherry.
Very useful …. Thanks for sharing Anshu Lilhori.
Regards
Subbrao M
Thanks Subbarao..
Really appreciate your comments.
Regards,
AL
Nice document. Thanks again..
Thanks Chandra for your feedback.
Regards,
AL
Hi Anshu,
Very good document , very useful. Nice way to escape from coding 😉 😉 …
Thanks for sharing.
BR
Aakash G
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
its good. Better approach. Thanks for sharing.
Thanks for your feedback..
Regards,
AL
Hi Anshu,
very good document with out CMOD 🙂 🙂 🙂
Thanks,
Phani.
Thanks Phani for your feedback on the same.
Regards,
AL
Really Helpful Anshu Lilhori 🙂
Regards,
Antony Jerald.
I am glad that its helpful for you.Thanks indeed.
Regards,
AL
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
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.
Am I missing something… appreciate any help..
Thanks
MF
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
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.
Hi,
Realy Very Nice Solution to achieve YTD without coding.
Regards
Jagan
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
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…!!!!???
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 ).
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