2 Replies Latest reply on Jul 7, 2016 6:49 PM by Anh Ho

    Group one dimension by another dimension

    Anh Ho

      Dear all,

      I have an Excel file which data showing the time the carpark is occupied in two days. I want to show the occupancy rate (in percentage) for each car park for two days.

      In other words, each car park has a fixed total time of  60 minutes per hour x 24 hours a day x  2 days = 2880 minutes. I was able to display total occupied minutes for each carpark as below:

      sample screenshot.PNG

      Each truck is categorize by a distinct call number and the activity number is the parcel number that has been loaded into the truck. As you can see here the turnaround time for each truck is the same. I want to:

      1/ group the turnaround time by the call number so for example, call no. 1001007 turnaround time is 105 minutes instead of (105 minutes x number of activity)

      2/ sum up the turnaround time for each distinct call number, divide by 2880 and show %

       

      Thank you

        • 1. Re: Group one dimension by another dimension
          Steve Burger

          Anh,

           

          I'm not sure I understand exactly, but I took a stab at it.  I'm making the assuption that a truck has only one turn around value, but that it's being (needlessly) replicated by the number of Activity No.s? For example, Call No. 1000008 actually only had a 60 minute turn around time, NOT a 60*4=240 minute turn around time?


          If so, take a look at attached WB, I've used the LOD calculation :  {FIXED [Call no.]: MIN([turnaround time (minutes)])} - to get the specific turn around time per Call No. and avoid the replication issue.  From there, it's a matter of dividing that LOD by 2880.

           

          If I understand correctly and have answered the question please mark it as helpful and/or correct so that others can find it quickly. Or respond with clarification.

           

          ScreenShot.PNG

          • 2. Re: Group one dimension by another dimension
            Anh Ho

            Steve,

             

            Yes you understand it correctly.

             

            THank you