Skip to Content
avatar image
Former Member

How to show Moving Annual Target in Crystal 4.1

Apr 19, 2017 at 07:06 AM | 46 Views

We would like to Show in Crystal a Line graph with the Moving annual target on our Bonus for the last two years. So basically for each value from the 31.01.2016 onwards we Need to sum the last 12 months for each record.

3keax.png (7.6 kB)
SAP Crystal Reports
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 19, 2017 at 02:17 PM

    No unfortunately they are not shown. I will try it out with the SQL command. Thanks

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Ian Waterman
    Apr 19, 2017 at 08:27 AM

    Not tried this but it might work for you, Place in detail or group footer where your amount is displayed.

    You might need to suppress for first 11 records

    numbervar the_field := {table.field}; // your value field

    numbervar array Accum;
    numbervar Rolling_Period := 12;

    if OnFirstRecord then
    ReDim Accum [1]
    else
    ReDim Preserve Accum [UBound(Accum)+1];

    Accum [UBound(Accum)] := the_field;

    If UBound(Accum) <= Rolling_Period then
    Sum(Accum) /Rolling_Period

    Else
    sum(Accum[(UBound(Accum) - (Rolling_Period-1)) to UBound(Accum)])/Rolling_Period;

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 19, 2017 at 12:20 PM

    Unfortunately it did not work. Below in yellow is the results using the new formula. I have found away to calculate the value but in a very convoluted way. For each month, I have created a formula where I save the months value. I then went and created a formula to sum the various values together. This gives me the correct values (shown in the "Correct result" column below), but I still have the Problem then on how I get to Show this in a line Chart and there must be a simplified way of doing this or?


    enuuq.png (32.3 kB)
    0u8bd.png (12.8 kB)
    Add comment
    10|10000 characters needed characters exceeded

    • Ian Waterman
      Apr 19, 2017 at 12:53 PM

      Sorry I did not read your post correctly I thought you wanted a moving average. Just remove the /Rolling_Period.

      Are neither of the formula available for use in the chart expert?

      Failing that you may have to change report to use a Command and determine Moving Total in SQL. Not done it before but I did a quick search and there are lots of posts on Google about how to build moving averages in SQL. (just exclude the divide by number of records)

      Ian