topic Re: How to count distinct while having to use a SUM in QlikView

How to count distinct while having to use a SUM

maudifer — Tue, 15 May 2018 15:35:30 GMT

Hi All,

I am building a KPI and I have created this expression:

Sum ({$<[MonthID1]={$(cCurrMonthID)},[Inbound Type]={'FULL CONTAINER LOAD'}>}TEU)

It works but I have mutliple lines for the same container in my raw data and I need to sum the TEU for only one container at a time.

How can i integrate Count( distinct( to my expression ?

I am summing the numeric values in the filed TEU but I need to it only once per container ref from the field [Container No.].

How can i do that ?

Re: How to count distinct while having to use a SUM

sunny_talwar — Tue, 15 May 2018 15:49:54 GMT

May be this

Sum({$<[MonthID1] = {$(cCurrMonthID)}, [Inbound Type] = {'FULL CONTAINER LOAD'}>} Aggr(Sum(DISTINCT {$<[MonthID1] = {$(cCurrMonthID)}, [Inbound Type] = {'FULL CONTAINER LOAD'}>}TEU), Container))

Re: How to count distinct while having to use a SUM

maudifer — Wed, 16 May 2018 13:15:05 GMT

Hi Sunny

It did not work. It showed 0 instead of a figure

Re: How to count distinct while having to use a SUM

sunny_talwar — Wed, 16 May 2018 13:15:41 GMT

Would you be able to share a sample to see the issue?

Re: How to count distinct while having to use a SUM

maudifer — Wed, 16 May 2018 13:34:10 GMT

The issue is it is my customer's data so i can't really share it

but basically in my raw data, there are multiple lines for each container

but i calculate the number of teu per container.

So for now, if there are 3 lines for a contiainer, it adding all 3 lines in the total sum rather adding just one.

Example:

RAW DATA

Container No.	Container Size	TEU
APHU6932662	40ft	2
APHU6932662	40ft	2
APZU4790601	40ft	2
APZU4790601	40ft	2
TEMU3683930	20ft	1
TEMU3683930	20ft	1

pivots:

What i get now what i need to get

APHU6932662	4	2
APZU4790601	4	2
TEMU3683930	2	1

Is that helping ?

Re: How to count distinct while having to use a SUM

sunny_talwar — Wed, 16 May 2018 13:45:46 GMT

Do you have more than one dimension in your chart or just Container No.?

Re: How to count distinct while having to use a SUM

maudifer — Wed, 16 May 2018 14:27:53 GMT

IN THE CHART I DON'T HAVE ANY DIMENTIONS

Re: How to count distinct while having to use a SUM

maudifer — Wed, 16 May 2018 14:31:22 GMT

See my pivot table: I only use Expressions ( in expression: a variable)

Re: How to count distinct while having to use a SUM

sunny_talwar — Wed, 16 May 2018 14:35:13 GMT

Not entirely sure... I think from what you initially provided... this should have worked

But if it did not... then I am not sure... may be if you can share a qvw sample... I might be able to take a look... otherwise I hope someone else can offer there help.

Best,

Sunny

Re: How to count distinct while having to use a SUM

maudifer — Wed, 16 May 2018 14:42:51 GMT

Thanks Sunny

I have to be careful about what I share I am getting someone else to look at it in my company

I appreciate the time you gave to my query