topic Re: Distinct sums in QlikView

Distinct sums

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

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

Re: Distinct sums

sergio0592 — Tue, 03 Jul 2018 18:34:49 GMT

Hi,

You can achieve 21k with as expression:

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

Re: Distinct sums

Anonymous — Tue, 03 Jul 2018 19:18:52 GMT

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

Re: Distinct sums

Anonymous — Tue, 03 Jul 2018 19:25:05 GMT

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

Re: Distinct sums

vishsaggi — Tue, 03 Jul 2018 19:38:49 GMT

What is your expected output?

Re: Distinct sums

Anonymous — Tue, 03 Jul 2018 19:41:04 GMT

In this case,

Marketing campaign	Total $ Won
A	21000

Re: Distinct sums

vishsaggi — Tue, 03 Jul 2018 20:04:40 GMT

Try this in your expr:

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

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

Re: Distinct sums

qlikviewwizard — Wed, 04 Jul 2018 03:25:01 GMT

Hi You can use this.

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