Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
igor_gershenson
Contributor III
Contributor III

Avoiding double counting in pivots

Here is a simplified version of the type of data I work with.  As you can see, it shows by marketing campaign which sales reps were involved, which deals were won, and how big each deal was.  

Name of Marketing CampaignSales repName of Deal Dollars from each deal
Program1John UMASS $ 1,000
Program1Drake BW $ 3,500
Program1JimTS $ 1,500
Program2BlakeUMASS $ 1,000
Program2Sofiebbc $ 1,000

I am trying to pivot this data but here is my challenge.

The deal called UMASS (worth $1000) appears in both programs because both marketing programs helped with it. What I want to do is allocate equal $ to program 1 and 2 for this deal (in other words, I want to assign 500 for program 1 and 500 for program 2 for this UMASS deal)instead of summing all the deals by campaign (which would result in double counting $). Ultimately I want to pivot by program so that the totals are shown like this. How do I do this in a pivot? Thanks so much.

   

Name of Marketing Campaign total dollars from deals
Program15500
Program2

1500

Grand Total7000
1 Solution

Accepted Solutions
sunny_talwar

Try this

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

View solution in original post

9 Replies
sunny_talwar

Try this

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

igor_gershenson
Contributor III
Contributor III
Author

Actually, I just realized that my data is a little more challenging than I originally thought. Here is a better example. There are two problems:

1) As before, the same deal (UMASS appears in multiple distinct campaigns)

2) Within the same campaign UMASS appears twice.

   

Name of Marketing Campaign
Sales repName of DealDollars from each deal
Program1John UMASS$1,000
Program1Drake BW$3,500
Program1JimTS$1,500
Program1BlakeUMASS$1,000
Program2IvanUMASS$1,000
Program2SofiaBBC$1,000 

Despite the two constraints above, I want the output of the pivot to be the same as before (i.e. within program 1, I want UMASS to be counted once in the pivot AND I want to allocate the UMASS deal between 2 campaigns equally (500 for program 1 and 500 for program 2). Have been struggling with this all day to no avail.

Name of Marketing Campaign total dollars from deals
Program15500
Program2

1500

Grand Total7000
sunny_talwar

May be 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]))

igor_gershenson
Contributor III
Contributor III
Author

Thank you but something is wrong with the count portion. it ends up counting all distinct marketing programs (2 in this case) regardless of which campaigns helped with each deal. So I have over 4k marketing programs, and that's what the denominator is for every campaign. hmmm

sunny_talwar

Would you be able to elaborate with an example?

igor_gershenson
Contributor III
Contributor III
Author

Sure but before I do, I wanted to make sure I am explaining everything correctly. The problem I am having is with this portion of the formula: Count(DISTINCT TOTAL <[Name of Deal]> [Name of Marketing Campaign]

It is meant to count how many distinct campaigns contributed to a deal but it ends up doing something else. It looks like it ends up giving the distinct count of marketing campaigns overall regardless of what deal we are looking at. SO my dataset has over 4k campaigns and this expression when used by itself gives me exactly that. Instead it should just count the number of distinct campaigns that contributed to each deal. Does that make sense?

sunny_talwar

But the reason we use TOTAL <[Name of Deal]> is to tell the expression to summ the campaign for each deal.... not sure why you get all... again... unless I see it... it might be difficult to understand the problem completely

igor_gershenson
Contributor III
Contributor III
Author

you are correct, as usual. I think I got it to work. User error.

sunny_talwar

Super awesome