Skip to Content
avatar image
Former Member

Compare data with the year before

Hello,

My name is Wessel van Erp and I'm student who just has started to work with Crystal Reports and SAP Business One. I'm really new to this kind of stuff. I've learned the basics using several tutorials and yt-videos, but at the moment I am stuck.

I am working on a cost report where I want to compare the total costs per account to the same time the year before. I managed to create a table to show the costs per account over a time set using a filter that's connected to a time range parameter. I want to compare these results to the exact same time the year before. So if the range is set as 01-04-2014 to 30-04-2014 it should automatically calculate the costs over 01-04-2013 to 30-04-2013. I want to show these two columns next to each other. So like this:

Costs by ledger Current Year before General costs xxx xxx Labor costs xxx xxx Depreciation xxx xxx Material costs xxx xxx

I use the JDT1 table to find the journals which are in the Debit and Credit column, and dates are entered in the RefDate column.

I'm sure there is a easy workaround for this, but i'm not able to find it. As I told I'm really new to this, so I really need some good help. It is possible that I explained it a bit unclear, so I'm standby for all your questions. I've searched for people with the same problems, but none helped. Probably because of my lack of skills. It is not that I'm lazy or something. 😉

Thanks in advance for helping me!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Dec 22, 2014 at 02:07 PM

    Hi Wessel,

    Try this please:

    1) Modify the existing record selection formula (Report > Selection formulas > Record) so that you're not restricting the records to just the range you've selected. It should be something like:

    {Database_date_field} IN [Minimum({?DateRangePrompt}) TO Maximum({?DateRangePrompt})]

    OR

    {Database_date_field} IN [cdate(Year(Minimum({?DateRangePrompt}))-1, Month(Minimum({?DateRangePrompt})), Day(Minimum({?DateRangePrompt}))) TO cdate(Year(Maximum({?DateRangePrompt}))-1, Month(Maximum({?DateRangePrompt})), Day(Maximum({?DateRangePrompt})))]

    2) Insert a Crosstab and place this on the Report Header

    3) Use the 'Costs by ledger' field as the 'Row' of the Crosstab. Use the Date field from the database as the 'Column' of the crosstab and choose the 'measure' field as the 'Field to summarize'

    4) While in the Crosstab Expert, highlight the date field under columns > choose Group Options > Where it says 'this section will be printed', choose 'For Each Year' from the drop-down.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you! This is not the way how I expected it to be, but it does it's job pretty well for now. I'm going to finetune it and if i have other questions I'll let it know.

      @Praveen: As you can see for now my problem is solved. But thank you for you help anyway!

  • Dec 22, 2014 at 12:40 PM

    Hi,

    For Last Year totals you need to Create below formulas to get the totals using Date range paramter value, it would be something like

    @StartDate:

    Cdate(Month(Cdate(Min{?Dateparameter})),Day(Cdate(Min{?Dateparameter})), Year(Cdate(Min{?Dateparameter}))-1)

    @EndDate
    Cdate(Month(Cdate(Max{?Dateparameter})),Day(Cdate(Max{?Dateparameter})), Year(Cdate(Max{?Dateparameter}))-1)

    @Total_For_Last Year
    if ({date})>= {@StartDate} and ({Date})<= {@EndDate} then {Total_Formula}

    --Praveen G

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Dear Praveen,

      First of all, thank you very much for taking time to help me.


      I started immediately, but I think i'm doing something wrong. It look likes it doesn't understand the parameters maximum and minimum. It tells me that it needs a date/time. I'm sure the parameter is set like it needs to be. When I create a separate formula that finds the minimum in the range, it displays the correct one. I tried to put that formula into the formula above, but it still says the same. I'm using the 2011 version, do you know what I possibly could have done wrong?

      Again, thanks for helping me! I really appreciate it.

  • Dec 22, 2014 at 02:07 PM

    Hi,

    If possible could you attach your report with saved data to this thread, so that we can test here..

    --Praveen G

    Add comment
    10|10000 characters needed characters exceeded