1 Reply Latest reply on Apr 2, 2018 5:36 AM by Aleksandar Antonov

    How to filter out unique rows for calculation

    Aleksandar Antonov

      HI All,

       

      I am trying to use data similar to the following concept below. What I would like to extract from this is the AVG Pick Up performance (time difference between assigned and picked up) by hour by client. Setting up the view is easy but AVG data gets distorted because the Order ID is not unique and for item picked up we have 2+ rows. To get an accurate estimate I would have to get only 1 of the of the rows of Order ID B. The standard aggregation on average would take into account this entry 3 times because the order ID is on 3 rows even though it has the same data.

       

      Set up is "Column": Picked Up time; "Rows": CNTD (Order ID), Client; "Color": AVG (ordering Performance) = difference between Picked Up time and Assigned time.

       

      I am limited to what I could do with the source data as it is connected to MySQL. When we used to make exports in Excel, we would color out the first row of the order ID differently and then repeated values of Order ID would be in other color, and then we would filter by color to make an extract. But since I am trying to connect all this to a live data source via MySQL I can't do any pre-filtration.

       

      In the attached TWB file, Client X at 19 hrs should aggregate to -0.011632 without one of the duplicate values, but in reality it duplicates to -0.011528 and that is what the color gets assigned at. Would be great if someone can recommend how to aggregate this somehow to the value I actually want which is 0.011632.

       

       

      Unfortunately I couldn't figure out myself how to filter unique rows only. Does anyone have an idea how I can handle this? Any help will be much appreciated.

       

      Message was edited by: Aleksandar Antonov