MTD,YTD Calculation formula

[#1]

Hi

Formula to calculate Month to date,year to date...

here is the scenerio

I am having having 2 prompts one is time period and other one is calender date

If the user selects MTD from time period..it need to calculate the data between the start date of the month to the date what ever the user selects in calender date

for example

If the user select calender date as 06/11/2013 and time period as MTD

I need to get all the transactions between 06/01/2013 till 06/11/2013

same with the year

I need tot get all the transactions between 01/01/2013 till 06/11/2013

any inputs appreciated

Thanks in advance

[former_member188911]

Hello,

this question is being asked almost every day and you will surely find a load of replies simply by searching

http://search.sap.com/ui/scn#query=YTD+prompt+webi+bex&startindex=1&filter=scm_a_site%28scam_v_Site1...

for more information please see the rules of engagement  http://scn.sap.com/docs/DOC-19331

thanks

Best regards,

Simone Caneparo

SCN Discussions Moderator

[Former Member]

Srikanth,

You may try  below approach with variables to achieve what you are trying to

Month start Date=Todate("01/"+Extract Month and year from UserResponse("Calendar Date");"dd/M/yyyy")

Year start Date=Todate("01/01"+Extract year from UserResponse("Calendar Date");"dd/M/yyyy")

MTD FLAG = If([calender date] between ([Month start Date];[Calendar date selected]);1;0)

MTD FLAG = If([calender date] between (Year start Date];[Calendar date selected]);1;0)

MTD YTD FLAG=If(UserResponse("time period")="MTD";[MTD FLAG];If(UserResponse("time period")="YTD";[YTD FLAG];""))

Finally, Apply a block filter as MTD YTD FLAG=1 to filter the data based on MTD or YTD accordingly.

Thanks

Mallik