Query: Making pct. calculations on cumulated rows.

[Former Member]

Hi Experts

I have a problem in a query. See the query below. The problem is that column 8 shows the same result as column 7 What I find strange is that column 6 is just right - it shows the YTD-deviation, which I would like to show in pct in culumn 8.

Does anybody know how I can make the %Dev on the YTD numbers in column 8?

Rows:

- Calendar month2 (monthnumbers: "1", "2"..."12")

Columns (Units sold):

1. Actual Month: current calendar year, billed quantity

2. Actual Month LY:current calendar year -1, billed quantity

3. Actual Year To Date: current calendar year, billed quantity (cumulated)

4. Actual Year To Date LY: current calendar year -1, billed quantity (cumulated)

5. Dev Month: Actual Month - Actual Month LY

6. Dev Year To Date: Actual Year To Date - Actual Year To Date LY<b>(OK)</b>

7. %Dev Month: Actual Month % Actual Month LY

8. %Dev Year To Date: Actual Year To Date % Actual Year To Date LY<b>(NOT OK)</b>

Kind regards and thanks in advance,

Torben

[Former Member · Accepted solution]

Hi,

It is also possible with CELL editor concept. For more info and for example search the forums.

I did no of similar reports with cell editor concept.

With rgds,

Anil Kumar sharma .P

[Former Member]

Does anybody have an idea please?

[former_member184494]

Torben,

The cumulation for the KF is done only in the front end and hence the cumulated value is not available to the OLAp processor and hence you cannot use the cumulated value in another formula.

This basically means that even if you use a cumulated figure in another formula you will still get the calculation on the basic value..

for example :

char|Basic Value|CUmulated Value|Cumulated value *2

A|1|1|2

A|2|3|4

A|3|6|6

If you notice row two it is taking the base value into 2 and not the cumulated value..

The only option for the same is to use a macro if the query is in excel or use the table modifier to change the displayed value..

Arun

Hope it helps...

[Former Member]

Hi Arun

Thank you for your reply. Unfortunately the macro-solution can not be used in my case since it is a webbased report.

What is the table modifier?

An alternative solution will be to use Cell Definitions I guess.

Regards,

Torben

[Former Member]

Hi Arun

I figured out hvad table modifier was. It does not seems to be a solution to 7.0. I think I will try to make use of table definitions.