Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
May be this
Sum({$<[MonthID1] = {$(cCurrMonthID)}, [Inbound Type] = {'FULL CONTAINER LOAD'}>} Aggr(Sum(DISTINCT {$<[MonthID1] = {$(cCurrMonthID)}, [Inbound Type] = {'FULL CONTAINER LOAD'}>}TEU), Container))
May be this
Sum({$<[MonthID1] = {$(cCurrMonthID)}, [Inbound Type] = {'FULL CONTAINER LOAD'}>} Aggr(Sum(DISTINCT {$<[MonthID1] = {$(cCurrMonthID)}, [Inbound Type] = {'FULL CONTAINER LOAD'}>}TEU), Container))
Hi Sunny
It did not work. It showed 0 instead of a figure
Would you be able to share a sample to see the issue?
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 ?
Do you have more than one dimension in your chart or just Container No.?
IN THE CHART I DON'T HAVE ANY DIMENTIONS
See my pivot table: I only use Expressions ( in expression: a variable)
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
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