How to count distinct while having to use a SUM

[maudifer]

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 ?

[sunny_talwar · Accepted solution]

May be this

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

[maudifer]

Hi Sunny

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

[sunny_talwar]

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

[maudifer]

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 ?

[sunny_talwar]

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

[maudifer]

IN THE CHART I DON'T HAVE ANY DIMENTIONS

[maudifer]

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

[sunny_talwar]

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

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

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

[maudifer]

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