4 Replies Latest reply: May 21, 2010 2:56 PM by rhadl1 RSS

    Rank with multiple dimensions and aggregation

    rhadl1

      Hi I'm really struggling to get this to work. What I need to do it to group several jobs by day and then report out the highest abandon rate by job/ day over each week.

      I've added an example where I can aggregate the jobs by type and by date, workout the overall ranking, and display just the one with the highest ranking, but I can not get this to work over multiple week. In the example there are 3 weeks, so there should be 3 rows displayed (highest rank for each week), but there is only one.

      Here's the data

      LOAD * INLINE [
      Week, Date, Type, JobNumber, Placed, Abandoned
      5/2/10, 5/2/10, Standard, 123, 100, 10
      5/2/10, 5/2/10, Standard, 124, 100, 10
      5/2/10, 5/3/10, Standard, 125, 100, 14
      5/2/10, 5/3/10, Advanced, 126, 100, 15
      5/9/10, 5/9/10, Standard, 127, 100, 30
      5/9/10, 5/9/10, Standard, 128, 100, 10
      5/9/10, 5/10/10, Standard, 129, 100, 14
      5/9/10, 5/10/10, Advanced, 130, 100, 15
      5/16/10, 5/16/10, Standard, 131, 100, 10
      5/16/10, 5/16/10, Standard, 132, 100, 10
      5/16/10, 5/17/10, Standard, 133, 100, 19
      5/16/10, 5/17/10, Advanced, 134, 100, 15
      ];


      Table output should be:
      Week, Date, Type, AbandonRate
      5/2/10, 5/3/10, Advanced, 15%
      5/9/10, 5/9/10, Standard, 20%
      5/16/10, 5/17/10, Standard, 19%

      Thanks in advance