Expressions for Pivots (avoiding double counting)

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Here is example of the data I am working with. There are two challenges with my pivot of this data.

1) The same deal (UMASS appears in multiple distinct campaigns)

2) Within the same campaign UMASS appears twice.

Name of Marketing Campaign	Sales rep	Name of Deal	Dollars from each deal
Program1	John	UMASS	$1,000
Program1	Drake	BW	$3,500
Program1	Jim	TS	$1,500
Program1	Blake	UMASS	$1,000
Program2	Ivan	UMASS	$1,000
Program2	Sofia	BBC	$1,000

Despite the two constraints above, I want the output of the pivot to be as shown below (i.e. within program 1, I want UMASS $ to be counted once in the pivot AND I want to allocate the UMASS totaL $ deal between 2 campaigns equally (500 for program 1 and 500 for program 2).

Output I want:

Name of Marketing Campaign	total dollars from deals
Program1	5500
Program2	1500
Grand Total	7000

Re: Expressions for Pivots (avoiding double counting)

sunny_talwar — Tue, 10 Jul 2018 00:59:37 GMT

Try this

Sum(Aggr(Sum(DISTINCT [Dollars from each deal])/Count(DISTINCT TOTAL <[Name of Deal]> [Name of Marketing Campaign]), [Name of Marketing Campaign], [Name of Deal]))

topic Re: Expressions for Pivots (avoiding double counting) in QlikView

Expressions for Pivots (avoiding double counting)

Re: Expressions for Pivots (avoiding double counting)