Distinct sums

[Anonymous]

  Here is a simple example of the type of data I am working with. I want to pivot it so that only $ for distinct opportunity names are added together. In other words, I want the sum of $ won to be 21k instead of 27k because I want the $ associated with hospital x to be counted only once. I can't use sum distinct because  it will treat UMASS and Hospital X as the same item and will only sum everything to 15k. Please help.  

Marketing campaign	LEAD Name	Opportunity Name	$ won
A	John	UMASS Hospital	6000
A	jonathan	Hospital X	6000
A	cindy	Hospital X	6000
A	Bob	Harvard Hospital	8000
A	Kate	Clinic A	1000

[sergio0592]

Hi,

You can achieve 21k with as expression:

if (not IsNull(aggr(RowNo(), [Opportunity Name])), sum([$ won]))

[Anonymous]

Thank you but I noticed that for this work my pivot must have Opportunity name as one of the dimensions. However, I want to be able to simply sum by campaign without showing individual opportunities. any way to achieve that? Thanks again

[Anonymous]

Sorry I meant to say that for this work my pivot has to have lead name as one of the dimensions. However, I want to be able to simply sum by campaign name without showing individual opportunities or leads. any way to achieve that? Thanks again

[vishsaggi]

What is your expected output?

[Anonymous]

In this case,

Marketing campaign	Total $ Won
A	21000

[vishsaggi]

Try this in your expr:

= Sum(Aggr(IF(Count([Opportunity Name])>1, Sum(DISTINCT [$ won]), Sum([$ won])), [Marketing campaign], [Opportunity Name]))

OR

= Sum(Aggr(IF(Count([Opportunity Name])>1, Only([$ won]), [$ won]), [Marketing campaign], [Opportunity Name]))

[qlikviewwizard]

Hi You can use this.

=if(count([Opportunity Name])>1,sum(Aggr([$ Won],[Opportunity Name])),sum([$ Won]))