3 Replies Latest reply on Aug 28, 2012 6:06 PM by hub.yoo

# Calculations involving incorrect aggregation of data, would like to remove a sub-sub-level of data

Hello,

I made a mock up of the problem i am having. I am curious about the approaches people have to circumvent this issue. I have 3 levels of data, order number, item description, and serial number. An order can have multiple items, and multiple quantities.

What I am trying to do is group all the items to just the order number and item description. This constitutes a unique order entry in my book. However, my calculations incorporate the serial number into the calc; when i have 1 order consisting of 3 item of variable quantities, i want to exclude the serial numbers so the table below has 3 unique instances instead of 6.

 8/16/2012 124505 Eggshells Limited Mechanical Pencil 30001-1 8/16/2012 1 8/16/2012 124505 Eggshells Limited Mechanical Pencil 30001-2 8/16/2012 1 8/16/2012 124505 Eggshells Limited Mechanical Pencil 30001-3 8/16/2012 1 8/16/2012 124505 Eggshells Limited Pen 50000-1 8/16/2012 1 8/16/2012 124505 Eggshells Limited Pen 50000-2 8/16/2012 1 8/16/2012 124505 Eggshells Limited Pencil 60000-1 8/18/2012 1

This should result in 3 groupings, and the last line is the only "group" that is late, so it has an on time percentage of 2/3, 66%. What i get in tableau is 5/6 or 83.3%.

Any help would be nice. Or if you are bored, say hello.

>" )

(___<_)<

• ###### 1. Re: Calculations involving incorrect aggregation of data, would like to remove a sub-sub-level of data

I've tried taking out the serial number from the data but it still agggregates at the sub sub level...

• ###### 2. Re: Calculations involving incorrect aggregation of data, would like to remove a sub-sub-level of data

I just learned something new: count distinct does not work on live connections to excel, access etc. So after i created an extract, I can do count distinct. i created a new field grouping the order number and description as a string and chose count distinct. This shows the correct values for number of orders but can't figure how to do a calculation based on this to yield the on time percentage i am looking for

• ###### 3. Re: Calculations involving incorrect aggregation of data, would like to remove a sub-sub-level of data

Got the viz to come out right, but took a lot of ridiculous, tortuous calculations. Will upload the packaged workbook "On Time 2".

If anyone has a more elegant solution, will still appreciate some feed back.