Year-To-Date Sales by month calculation in cube

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member]

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

[Former Member]

Rishi,

So, do I build my agggregate by ..CALMONTH or? And, I'd like to have a KYF YTDSALES to assign the amount there, so the query will run fast. How do I assign the amount to this column?

Thx, RL

[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