cancel
Showing results for 
Search instead for 
Did you mean: 

Display certain totals as average values in Evdre

Former Member
0 Kudos

Hi,

we have a planning layout in which the columns contain forecast previous year (2008.TOTAL) ,actual previous year and 12 columns of budget for current year (2009.JAN, ..., 2009.DEC). The rows have sales quantity and a couple of unit rates (sales and COS).

We would like the system to display the unit rates as averages instead of adding them up, while the sales volume should be added up. Is this possible?

regards

Dries

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Alwin,

Method 1: does not work, because each time changing the CV and expanding, the formula's are gone or are not expanded down.

Method2: does not work, because then I cannot input the unit rate anymore

Method 3:

I would like to avoid adding again logic in default (or having to call up a script again) ...

Method 4:

Could you explain this more in detail?

I was hoping the cell key range of an Evdre could solve my issue, but I haven't worked with this yet ...

Dries

Former Member
0 Kudos

Hi Dries,

I don't think that the cell key range of evdre can help you since this is only meant to be used for deviation of dimension members between the pagekey/rowkey/collkey ranges and an individual cell. It will not give you average data.

Concerning method 1:

You can build reports that expands in the rows and that have average formulas in one column that reference other columns.

Concerning method 4:

You can change the standard measures of an application. Normally an account has YTD and Periodics. You can add an other measure (or use an existing) that calculates an avg based on a property of an account, but be aware of possible performance risks.

When you have a periodic application, the measures are stored in the SQL table tblformula.

When you have a ytd application, the measures are stored in the SQL table tblytdformula.

Please process an application from administration after you changed the measures in the SQL table so that you can use them.

You can also create/change/try measures directly in Business Intelligence Studio.

Alwin

Former Member
0 Kudos

Hi Dries,

I think that there are a few ways to achieve this:

Method 1:

just calculate the average unitrate and sum sales directly in Excel

Method 2:

create an extra account for unitrate that has a dimension formula that calculates the average unitrate. sum sales can be achieved by using the standard measures in combination with acctype=INC or EXP.

Method 3:

create an extra account for unitrate and use sql logic to calculate the average unitrate. sum sales can be achieved by using the standard measures in combination with acctype=INC or EXP.

Method 4:

create/adjust the standard measures so that it calculates an average for unitrate and a sum for sales.

All methods have there advantages and disadvantages concerning usage and more important, performance.

Hope this helps,

Alwin