Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

0 Replies