Skip to Content
author's profile photo Former Member
Former Member

Year-To-Date Sales by month calculation in cube

Hi everyone,

I want to already calculate the 'YTD Sales'by month in my cube, so I increase performance when the user is running his report. YTDSALES = Actual Month Total Sales + Previous month YD Total Sales.

Not sure how to implement it in the update rule. Looks like a lt of coding. Any suggestions would be greatly appreciated.

Thanks, RL

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Feb 24, 2005 at 10:56 AM

    You should do a little loop in the start routine and write all the monthly values into any periods up to period 12. The coding could look like:

    data: l_d_data_package like line of data_package,
          l_t_data_package TYPE STANDARD TABLE OF  
                           DATA_PACKAGE_STRUCTURE
                           WITH HEADER LINE
           WITH NON-UNIQUE DEFAULT KEY INITIAL SIZE 0.
      loop at data_package into l_d_data_package. 
        l_d_data_package-/bic/ytdsales = 
             l_d_data_package-/bic/sales. "or other keyfig
        while l_d_data_package-calmonth+4(2) <= '12'.
          append l_d_data_package to l_t_data_package.
          clear l_d_data_package-/bic/sales. "or other keyfig
          l_d_data_package-calmonth+4(2) = 
             l_d_data_package-calmonth+4(2) + 1.
    *     If you have fiscper or fiscper3 or 
    *     calquarter you may need to adjust it here too.
        endwhile.
      endloop.
      data_package[] = l_t_data_package[].
    

    But you should first check if aggregates or precalculated web templates are better.

    Best regards

    Dirk

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Feb 23, 2005 at 11:44 PM

    Hi Ramona,

    Not saying that it cannot be done, but you might burn your fingers trying to implement this requirement. The reason I mention this is that changes to a previous month value could mean change to the entire years data. E.g. If a sale is registered in Jan, you need to iteratively change all the YTD sales thru Dec.

    Have you tried using aggregates instead. Might be a quicker and easier to maintain solution. I am suggesting this blindfolded as I don't know the layout of your cube, but I am positive, that you should see performance gains by doing this.

    Hope that helps!

    Rishi

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Ramona,

      The aggregate needs be be created for all the characteristics in the report including time characteristic (in this case 0CALMONTH if thats what the report is run for).

      Additionally, you cannot populate a KY in aggregate. But the advantage of using the Aggregate is that the "Actual sales" KF will be rolled up for the the relevant combinations of Charecteristics in the Cube for individual Calendar months in this case.

      Once again, it would really help if you could let me in on the definition of the cube!

      Rishi

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.