Question:
I used to believe with BW 3.0B you could do any calculation you can do in Excel, but then I came across the following problem:
A key figure (DSO- daily sales outstanding) needs to be calculated in BW that is defined recursively on other key figures on an aggregated level.
It canNOT be calculated on the individual record in a transfer/update rule
(as far as I can see).
The definition of DSO is how many of your last days's sales are equivalent to your current trade receivables:
Monthly sales are deducted from your receivables going back month by month as long as the value is positive. The number of months deducted are then multiplied by 30. For the month leading to a negative value a portion of 30 is calculated and added to it.
Is that possible with the Query Builder???
Any work arounds (apart from BExReport with Excel calculation)?
Thank you for ideas.
BusyWare
Answer:
Have you looked at the business content queries on the 0fiar_c02 cube that have DSO in them. This might answer your question or point you in the right direction. Hope this helps.
thanks,
jlaruss
Answer:
I used to believe with BW 3.0B you could do any calculation you can do in Excel, but then I came across the following problem
I believe you are referring to the Cell Editor with which you can have "exceptional calculations" (don't really know how to call it in English, but I hope you get my point). Let me give you an example of a perfect use for this. Suppose you have a report which shows some Key Figures (e.g. Invoiced Quantity, Gross Sales, Contribution) in the rows and months in the Columns. It would look something like this :
...............................January........February
Invoiced Quantity........X1.................X2
Gross Sales................Y1..................Y2
Contribution................Z1.................Z2
Suppose that next to each months figures you wish to see an amount per unit (e.g. Gross Sales per KG). You'll need to report:
..............................January.../unit.......February.../unit
Invoiced Quantity.........X1..........1........X2............1
Gross Sales.................Y1.......Y1/X1.....Y2.........Y2/X2
Contribution................Z1.......Z1/X1.....Z2.........Z2/X2
To achieve this you need to define "special" formula's on the cells in the "/unit" columns, because this is not possible in the query designer as such. Within the Cell Editor you can have a different formula for each cell.
I'm not sure whether this will work for you, but I suggest you start playing with it (if you have the time of course).
cheers,
El Belgio