Join / Merge actual and target values

[konHi]

Hi,

i have the following problem and don't see the solution how to solve it (tried a lot of things but still don't get it).

I have two tables:

1: actual sales

2: targets

Actual Sales:

RowNr.	Dep.	Main. Dep	Area	Plant	Modell	Sales
1	A	BB	CDE	1	-	5
2	A	BB	CDF	1	-	5
3	A	BB	FDE	1	-	12
4	A	BB	FDE	2	-	5
5	B	BB	FDE	-	-	16
6	A	BC	-	-	Mod1	17

2: Target

RowNr.	Dep.	Main. Dep	Area	Plant	Modell	Target
11	A	-	-	-	-	12
12	A	BB	-	-	-	8
13	-	BB	-	-	-	9
14	A	BB	FDE	-	-	13
15	-	-	-	-	Mod1	5
16	-	BC	-	1	-	8
17	A	-	-	1	-	12

As a result I want to have following in Pivot Table:

RowNr.	Dep.	Main. Dep	Area	Plant	Modell	Sales	Target
21	A	-	-	-	-	44	12
22	A	BB	-	-	-	27	8
23	-	BB	-	-	-	43	9
24	A	BB	FDE	-	-	17	13
25	-	-	-	-	Mod1	17	5
26	-	BC	-	1	-	0	8
27	A	-	-	1	-	22	12
28	A	BB	-	1	-	22	-
29	-	BC	-	-	Mod1	17	-

There is no real hierarchy in the structure. I always want to have the sum(Sales) and the Target at the specific level. (RowNr. is only for reference. )

For Example:

Example RowNr:	Pivot Dimensions	Sales Expression	Target Expression
22	Dep. -> main.Dep: A->BB	=sum(Row1+Row2+Row3+Row4) = 27	=Target from Row 12 = 8
24	Dep. -> main.Dep ->Area: A->BB-FDE	=sum(Row3+Row4) = 17	=Target from Row 14 = 13
26	Main.Dep ->Plant	=sum(Not Available) = 0	=Target from Row 16 = 8
28	Dep. -> Main.Dep. -> plant	0sum(Row1+ Row2 + Row3) = 22	=Target now available = -

I hope you understand my problem. I am very grateful for you answers. Please find attached the QVW with the data. thank you very much

[Anonymous]

Have you tried to concatenate Sales and Target?

See attached

[konHi]

Hi Michael,

thank you for the answer. The problem is, I am not allowed to aggregate the target through sum. I have to pick the right target at the right level. In you app, i get for Dep: A Target 24 or 45. But I should pick the RowNr21 and show as Target: 12.

[Colin-Albert]

Is there another field that can be used to associate the rows you are summing for the Sales value and the Target rows instead of trying to associate the data by row numbers. - A WeekNo, or Period//Year would be the ideal option.

If you could concatenate the Sales & Target data , then you could simply sum the Sales and Target data against a Period field, to give the chart you are looking for.

Concatenating Sales and Target data is the simplest solution to ensure that you do not have an incorrect join causing target values to be counted multiple times.

[sunny_talwar]

You are not going to like this, but it kind of does the job for you:

=If(Len(Trim(Main.Dep)) = 0 and

  Len(Trim(Plan)) = 0 and

  Len(Trim(Modell)) = 0 and

  Len(Trim(Area)) = 0, Sum(TOTAL <Dep.> Sales),

If(Len(Trim(Dep.)) = 0 and

  Len(Trim(Plan)) = 0 and

  Len(Trim(Modell)) = 0 and

  Len(Trim(Area)) = 0, Sum(TOTAL <Main.Dep> Sales),

If(Len(Trim(Dep.)) = 0 and

  Len(Trim(Plan)) = 0 and

  Len(Trim(Main.Dep)) = 0 and

  Len(Trim(Area)) = 0, Sum(TOTAL <Modell> Sales),

If(Len(Trim(Plan)) = 0 and

  Len(Trim(Modell)) = 0 and

  Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Main.Dep> Sales),

If(Len(Trim(Main.Dep)) = 0 and

  Len(Trim(Modell)) = 0 and

  Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Plan> Sales),

If(Len(Trim(Modell)) = 0 and

  Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Main.Dep, Plan> Sales),

If(Len(Trim(Modell)) = 0 and

  Len(Trim(Plan)) = 0, Sum(TOTAL <Dep., Main.Dep, Area> Sales),

If(Len(Trim(Area)) = 0, Sum(TOTAL <Dep., Main.Dep, Plan, Modell> Sales), Sum(Sales)))))))))

You may need to add more If statements here, but it kind of get the output you are looking for.

Attaching the qvw for reference as well.

Best,

Sunny

[konHi]

Hi Sunny,

it seems to work. quit complicated but my first testing was positive. Thank you very much!!!

I will keep you up to date.

Have a nice day

[edit]: ist that right, that i will have 2^[number of fields] possibilities for the if clause? At the end i will have 12 Fields with targets in every possible combination = 2^12 = 4096

[sunny_talwar]

Yup and that was the reason I said that you are not going to like this formula. Am sure there must be a better way to do this, but I just don't know how to do it

Best,

Sunny

[konHi]

Hi Sunny,

I tried to make the expression dynamic. Find the example attached. The "String" in the concatenated row seems to work but the evaluated row does not evaluate the string properly. Do you see any mistakes?

Thank you,

Konstantin

[sunny_talwar]

I really liked the idea of what you are doing, but I think the problem is that the expression is always evaluated as false and giving you an output for the false statement which is Sum(Sales). Take a look at the image below:

We somehow need to make it to evaluate the if statement and give an output. But I really liked the idea of what you have done.

Best,

Sunny

[sunny_talwar]

I kind of understand the problem now. Your evaluated Sales new is getting evaluated at the chart level instead of row level. The way I was able to figure this out was by selecting Main.Dep BB and seeing the result as 43 instead of 0 when everything else was null (See the image below)