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

[konHi]

Thank you, after trying to figure out how to force qlikview to evaluate at row level I am still not getting any progress. I tried to evaluate a dynamic set analysis in an expression. Still not getting what i want (set analysis is evaluated on the chart level too)

The problem should not be that difficult but I don't see the path to solve the problem.

Any other suggestions? I tried to do it with "generic keys", no progress 😞

[konHi]

My new approach was to create two fact tables and in Expression:

=only(if(

  if( vShowDep=1, Dep.=T_Dep., T_Dep. = '')

  and (if( vShowMainDep=1, Main.Dep=T_Main.Dep, T_Main.Dep = ''))

  and (if( vShowArea=1, Area=T_Area, T_Area = ''))

  and (if( VShowModell=1, Modell=T_Modell, T_Modell = ''))

  and (if( vShowPlan=1, Plan=T_Plan, T_Plan = ''))

  , T_Target))

it works, but for the calculation QlikView creates an cartesian product (in the background) very very bad performance. Any idea in tuning this?

[Colin-Albert]

I think you need to re-evaluate the data model and the table structure of your data.

Consider concatenating your actuals and target data as initially suggested by mov‌ and others.

[konHi]

Hi Colin, thank you for you answer.

The problem is, a simple concatenation and then sum(Target) does not work, because qlikview sums all targets with Dep = A for example. but it should only show the target where Dep = A and all other fields are empty. If i Want to show Dep. and Main.Dep then only the Target where dep. and Main. Dep has values should be shown.

[Colin-Albert]

If your data has a common Dep dimension field  on the actual and target records, then having the Dep as a dimension of your chart will ensure that sum(Target) will show the relevant data for each Dep.

You will need to have relevant dimensions on both the actual and target records.

Ignore the row numbers and analyse your data by the dimensions.