How to calculate YTD,MTD and QTD values

[vinaya_hg]

Hi folks,

I am beginner in BO.I am using BO 4.2 and have a scenario to create YTD ,MTD and QTD values for particular customer based on date given in prompt by creating variables . In the Query filter I have created filter on date to fetch the data between the range of dates .One date is constant the other one is from prompt.The formulas I used to calculate values are:

For YTD,

=[measure] Where ([year]=[p_year] And [month_no]<[p_month] And [day]<=31) + [measure] Where ([year]=[p_year] And [month_no]=[p_month] And [day]<=[p_day]).

For MTD ,

=[measure] Where ([year]=[p_year] And [month_no]=[p_month] And [day]<=[p_day])

For QTD,

=[measure] Where ([year]=[p_year] And [Quarter]=[p_quarter] And [day]<=[p_day])

where, [year] = calendar year ,[p_year]=prompt year, [month]= calendar month ,[p_month] = prompt month , [day]= cal day , [p_day] =prompt day ,[p_quarter] = Quarter in which the prompt date lies.

The problem I am facing is , unable to fetch the accurate values for the variables.

Could someone tell me whether the formulas I am using is correct?

Regards,

Vinaya

[mhmohammed · Accepted solution]

Hi Vinaya,

Try to use the formulas from this blog to get the MTD, QTD and YTD. You'll have to replace the Current Date with Todate(Userresponse("Enter Date");"") though. After that, you can easily get the Measure values.
https://blogs.sap.com/2014/01/21/time-variablesdimensions/

Thanks,
Mahboob Mohammed

[kohesco]

Please calculate such kind of calculations in back-end (universe or BEX query)

[former_member603552]

Could you please explain little bit more, how to calculate the same in universe.

Ex: If we want to show the data in MTD, if we ran the report in 20th of auguest then report showuld show the 1st of aug to 20th of aug.