Create fields within dimension using existing fields

[#1]

Hi all,

Hope you can help me, really need this solved but dont know how.

I'm loading a lot of data on customer and product level. One dimension is named "Type" and contains the Fields "Sellings Costs" and "Net Sales Value" and more.

The dimension "Type" is created by a separated file, the original sales data cointains GL acounts. A group of GL Accounts creates a Type.

In the load script I first load the sales data on GL account level and already group some GL accounts when using the "+" or "-" function. So far no problem.

Now I want the Type "Selling Costs" /  "Net Sales Value" as "Selling Costs %".

The problem is that this number is put into the datatable as a fixed number. If I create a chart that shows data on a higher level than customer and product, that the values of "Selling Costs %" are being summed.

So if I have one customer that buys two products and on both products he receives 50% Selling Costs on the Net Sales Value and I present the sales of this account in the chart but not on the product level, the outcome is 100%. This is 50% + 50%. The correct outcome is 50%.

For the presentation it is required that I use only one column / dimesion. The expression should also be only one colomn. So it looks something like this:

Type	Budget	Actual
Net Sales Value	100	90
Sellings Costs	25	30
Selling Costs %	0.25	0.33

Anybody got an idea?

Thanks in advance,

René

[#2]

Rene,

I think you are using an expression like

Sum([Selling Costs]/[Net Sales Value])

Switch to the below:

Sum([Selling Costs])/sum([Net Sales Value])

Kiran.

[#3]

Thanks Kiran,

But the problem is in the loadscript, are you sure tthis is what you mean? Cause I cant type your sollution in the loadscript.

thanks Ren'e

[#4]

Thanks Kiran,

But the problem is in the loadscript, are you sure tthis is what you mean? Cause I cant type your sollution in the loadscript.

thanks Ren'e

[#5]

Thanks Kiran,

But the problem is in the loadscript, are you sure tthis is what you mean? Cause I cant type your sollution in the loadscript.

thanks Ren'e

[#6]

Thanks Kiran,

But the problem is in the loadscript, are you sure tthis is what you mean? Cause I cant type your sollution in the loadscript.

thanks Ren'e

[#7]

Thanks Kiran,

But the problem is in the loadscript, are you sure tthis is what you mean? Cause I cant type your sollution in the loadscript.

Thanks René

[#8]

Thanks Kiran,

But the problem is in the loadscript, are you sure tthis is what you mean? Cause I cant type your sollution in the loadscript.

Thanks René

[#9]

Yes. This is not for load script. Can you share your data model for suggesting changes?

Kiran.

[#10]

Hi Kiran,

You answer ws so far helpfull that I decided to build the data structure differently. Now we make the Selling Costs % in an expression.

I tried to avoid this but I think its the only way.

So thanks for thinking with me.

Ren'e