topic Re: Create fields within dimension using existing fields in QlikView

Create fields within dimension using existing fields

Thu, 26 Jul 2012 13:56:05 GMT

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é

Re: Create fields within dimension using existing fields

Thu, 26 Jul 2012 16:14:06 GMT

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.

Re: Create fields within dimension using existing fields

Fri, 27 Jul 2012 08:00:02 GMT

Thanks Kiran,