3 Replies Latest reply: Jan 19, 2012 10:45 AM by Igor Alcantara RSS

    How to show the Max of one column compared to another column?

    Igor Alcantara

      I have one table with basicaly 3 columns:

       

       

      C DateField B
      1 1/5/2012 105
      1 1/6/2012 123
      1 2/3/2012 134
      1 2/7/2012 122
      1 2/24/2012 154
      1 3/4/2012 155
      1 4/13/2012 213
      1 4/23/2012 178
      1 5/4/2012 189
      1 5/23/2012 187
      1 7/7/2012 123
      2 1/1/2012 100
      2 1/3/2012 110
      2 1/4/2012 103
      2 1/5/2012 105
      2 3/23/2012 145
      2 4/23/2012 178
      2 6/6/2012 11
      3 1/1/2012 100
      3 1/2/2012 101
      3 1/6/2012 888
      3 2/24/2012 154
      3 3/23/2012 145
      3 4/12/2012 134
      3 4/13/2012 213
      3 5/4/2012 189
      4 1/1/2012 100
      4 1/2/2012 101
      4 1/3/2012 110
      4 1/4/2012 103
      4 2/9/2012 122
      4 2/9/2012 165
      4 3/4/2012 155
      4 4/13/2012 213
      4 5/4/2012 189
      4 5/23/2012 187
      4 7/7/2012 123
      5 1/2/2012 101
      5 1/3/2012 110
      5 1/4/2012 103
      5 2/3/2012 134
      5 2/7/2012 165
      5 2/24/2012 154
      5 3/4/2012 155
      5 3/23/2012 145
      5 4/12/2012 134
      5 4/23/2012 178
      5 6/6/2012 11


       

      I want a chart that shows for every C, the first DateField for the max B.

       

      I cannot do that in the load script because the Max depends on my selections. So, let's say I select days 7 to 13. This is the records I get:

       

       

      C DateField B
      1 2/7/2012 122
      1 4/13/2012 213
      1 7/7/2012 123
      3 4/12/2012 134
      3 4/13/2012 213
      4 2/9/2012 122
      4 2/9/2012 165
      4 4/13/2012 213
      4 7/7/2012 123
      5 2/7/2012 165
      5 4/12/2012 134

       

       

      For that selection, I expect a chart like this:

       

      C DateField B
      1 4/13/2012 213
      3 4/13/2012 213
      4 4/13/2012 213
      5 2/7/2012 165

       

      In the case the Max of column B returns more than one row for the same column C, the Datefield can be the 1st one.

       

      How can I do that?

       

      I am attaching a QVW where I have this data loaded.

       

      Thanks in advance.