Skip to Content
0

SAP Crystal Report Previous month total amount formula

Jan 10 at 04:44 PM

63

avatar image

Dear SAP Experts,

I have designed a report and I want a formula that will bring the previous month total amount into the current date range values parameter.

Example, if I run a report from January 1 2018 to 31 Jan 2018 , I want to have the total amount of 1-12-2-17 to 31-12-17 value showing in another column called Previous Month value.

Please treat this an urgent and help out

Regards

Justice

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Nagarajan K Jan 10 at 10:41 PM
0

Hi,

Use below example in your query to get last month,

SELECT T0.[DocNum], datediff(m, T0.[DocDate],getdate())-1 FROM ORDR T0

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Brian Dong Jan 10 at 06:30 PM
0

Hi Justice,

Your report will have to pull data from both your current month and previous month. How you do that will depend on how your report is formatted. You can query the database to return both months of data and in one formula calculate it for the current month and in the previous formula query for the previous month.

You can use a subreport to query and calculate the data for the previous month.

In either case, you will need to somehow get the data for both months.

Good luck,

Brian

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Brian,Please assist with steps or formula to guide me to achieve what you have suggested.

0

Hi Justice,

You will need to have the report return data for the previous and current month.

In the Detail section, create a formula like:

@current

If {table.DateField} in [Date(2018, 01, 01) to Date (2018,01,31)] Then 
    {table.orderamount} 
Else 0; 

Now create a second formula like:

@previous

If {table.DateField} in [Date(2017-12-01) to Date (2017-12-31)] Then 
    {table.orderamount} 
Else 0;

So you now have 2 columns that will display the totals for current and previous months. You can now Sum them as you normally would.

I hard coded the dates because how your report handles the dates would determine how to code the current and previous date range. Try this first and then if you need to, explore how to set the date ranges through code.

Brian

0