Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
maudifer
Contributor III
Contributor III

How to count distinct while having to use a SUM

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 ?

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

9 Replies
sunny_talwar

May be this

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

maudifer
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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 SizeTEU
APHU693266240ft2
APHU693266240ft2
APZU479060140ft2
APZU479060140ft2
TEMU368393020ft1
TEMU368393020ft1

pivots:

                                                  What i get now                         what i need to get

   

APHU693266242
APZU479060142
TEMU368393021

   

Is that helping ?

sunny_talwar

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

maudifer
Contributor III
Contributor III
Author

IN THE CHART I DON'T HAVE ANY DIMENTIONS

maudifer
Contributor III
Contributor III
Author

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

Capture.JPG

Capture 2.JPG

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
Contributor III
Contributor III
Author

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