Don't get it done....

[VisioX]

I have a problem with some report calculations...
My skills in explaining are not the best, so i try it with a picture...

Here is my crosstab...

The cells for Region/cType and Year/Monthname short are filtered on Year this way:

Code Select Expand

if (
[Revenue Analysis].[Rental Revenue Composition By Month].[MEDATE] between _add_months(_first_of_month(current_date),-13) AND _add_months(current_date,-1)
) then (
[Revenue Analysis].[Calendar].[Year]
) else ( NULL )

CYTD Avg is created this way:

Code Select Expand

if (
[Revenue Analysis].[Rental Revenue Composition By Month].[cYear] = extract(year,_add_months(current_date,-1)) and [Revenue Analysis].[Rental Revenue Composition By Month].[cMonth] <= extract(month,_add_months(current_date,-1))
) then (
_round([Revenue Analysis].[Rental Revenue Composition By Month].[SDPerc]/
extract(month,_add_months(current_date,-1)),4)
) else (0)
So far so good. No problems at this point.

The rows "Daily / Weekly / Monthly - Average" are created that way:

Code Select Expand

[t1]/[Count(Region)]
(t2 and t3 for weekly and monthly)
t1 looks like this:

Code Select Expand

if (
[Revenue Analysis].[Rental Revenue Composition By Month].[cTOrder]=1
) then (
[Revenue Analysis].[Rental Revenue Composition By Month].[SDPerc]
) else (0)
For weekly and monthly i use cTOrder = 2 (3) in [t2] and [t3]

The row "Count(Region)":

Code Select Expand

count(distinct [Region])

Rows "Count(Region) / t1 / t2 / t3" are NON-DISPLAY
Ok, so far everything is still fine

BUT

for the rows "Daily Average / Weekly Average and Monthly Average" i am not able to get my "CYTD Avg / PYTD Avg / Trend Avg" column filled with some usefull data. Which means it is blank. I try to solve the problem now since 2 weeks but I am at the end of my knowledge and hope that some of you get help me.

We are running right now on 8.1

Thanks

[blom0344]

Your example is a little over my head , but from what I have read in the KB the solve order is a mayor element in getting complex calculations to work properly. In your case it looks like you need to raise the solve order of the last calculation to a value 1 higher than those that appear to work.

[Suraj]

VisioX,
All you have to do is to change the aggregate properties to NONE and Rollup aggregate properties to Calculated for those rows that are blank.
That's all.

[VisioX]

VisioX,
All you have to do is to change the aggregate properties to NONE and Rollup aggregate properties to Calculated for those rows that are blank.
That's all.

That doesn't help. Now the complete row is 0, except for the last 3 columns; they are still blank/empty.
if i set it back to auto/auto, i get at least the cells under Year/MonthNameShort filled. But the 9 cells under CYTDAvg/PYTDAvg and Trend for the last 3 rows are still blank/empty.

Maybe I wasn't clear enough...
The last 3 rows are calculated "_{[t1]/[Count(Region)]}" but i can't find a way to get an average in the last 3 columns for the last 3 rows.

[Suraj]

You can create separate calculations for those cells.
Click on the cells, on the properties, set define cell content to Yes.
Then unlock the report and add custom calculation in that cell.
That way you can control what is displayed in those cells.

[VisioX]

I am already doing that.
But how do I get the values from "Daily Average" into my calculation?
Plus, for example, CYTD can have "current year" values only.