Combine data from pivot table with table box and add a calculated field

Tue, 26 Sep 2017 19:40:05 GMT

Hi,

I was wondering if anyone would be able to help me.

1. I want to combine data from my pivot table and table box to form one big table.

2. I want to add 3 more columns to the end of this new table

a. Column 1 would be a calculated field called "difference" which will subtract the values in the current period from the prior period.

b. Column 2 will be a logical formula that will indicated whether a deal has been booked ect..

c. Column 3 will allow sales team to provide comments on specific deals.

Please see below sample data that will be used and the desired result.

Pivot Table

	SUM (Probability %)		SUM (CNR($M)		SUM (AUM ($MM)
Period Deal No.	Current	Prior	Current	Prior	Current	Prior
5232091	25	50	457884	362327	111451	121964
3894839	50	75	533442	323211	434566	435627
4927995	100	0	246711	923451	123456	378925

Table Box

				AUM ($MM)
Deal No.	Region	Banker	Sales member	Current	Prior	Difference
748591	Canada	Tony	John	76	34	42
432040	Western	Hannah	Rachel	67	23	44
129041	Eastern	Rose	Temi	78	42	36

Desired result

				AUM ($MM)			Review	Comments
Deal No.	Region	Banker	Sales member	Current	Prior	Difference	Booked deal
74859	Canada	Tony	John	76	34	42	AUM variance
43204	Western	Hannah	Rachel	67	23	44	CNR Variance
12904	Eastern	Rose	Temi	78	42	36	Booked deal

The end goal is to reproduce an existing report in an excel that will make it easier for the sales team to view their deals and to provide comments on the ones that are flagged up by the formula in Column 2. I welcome any suggestions on the best way to achieve this Please let me know if you require further clarification.

Many thanks,

Esther

topic Combine data from pivot table with table box and add a calculated field in QlikView

Combine data from pivot table with table box and add a calculated field