6 Replies Latest reply: Oct 6, 2011 11:57 AM by zack.lore RSS

    Max Function and respective Date on Chart

      Hi All,

       

      I have a chart which has data like this

      Namevalstart dtend dt
      Jon10009/23/2011 10.15 AM09/23/2011 10.30 AM
      Jon20009/23/2011 10.35 AM09/23/2011 10.45 AM
      Jon30009/23/2011 10.48 AM09/23/2011 10.55 AM
      Jon40009/23/2011 11.00 AM09/23/2011 11.30AM


      I need to create another chart which can show the max value and the time of occurance

       

      Namemax-valstart dtend dt
      Jon40009/23/2011 11.00 AM09/23/2011 11.30AM


      In the chart I used name as dimension and created an expression max(val) which provides 400,But I am not sure how to bring the corresponding start time and endtime.This needs to be handled on the Chart.

       

      Anyone can help?

       

      Thanks..

        • Max Function and respective Date on Chart

          maybe something like this for your expressions:

           

          Only({$<Value = {"$(=Max(Value))"}>} startDate)

           

          Only({$<Value = {"$(=Max(Value))"}>} endDate)

            • Max Function and respective Date on Chart

              Thanks for the answer..

               

               

              if there are multiple names and I need to find max(val) for each name and corresponding startdatetime and enddatetime then above answer needs a tweak.

               

              need to show in the chart

               

              jon   400    9/23/2011 11AM       9/23/2011 11.30 AM

               

              Kim 500    9/25/2011 9.50 AM     9/23/2011 10.55 AM

               

              above answer only helps me to show Kim's value because that is highest between Jon and Kim.

               

              if anyone can help on this would be apprecited.

                • Max Function and respective Date on Chart

                  So I made variables for the max of each name.  I used Bob, Joe and Jose.  So for someone named Bob:

                   

                  vMaxBob:  =Max({1<Name = {'Bob'}>} Value)

                   

                  Then within the chart have on expression for the Max:

                   

                  aggr(Max(Value), Name)

                   

                  Then for the date start have:

                   

                  if(Name = 'Bob', ONLY({$<Value = {$(=vMaxBob)}>} DateS),

                            if(Name = 'Joe', ONLY({$<Value = {$(=vMaxJoe)}>} DateS),

                                      if(Name = 'Jose', ONLY({$<Value = {$(=vMaxJose)}>} DateS),

                                      )))

                   

                  and date end:

                   

                  if(Name = 'Bob', ONLY({$<Value = {$(=vMaxBob)}>} DateE),

                            if(Name = 'Joe', ONLY({$<Value = {$(=vMaxJoe)}>} DateE),

                                      if(Name = 'Jose', ONLY({$<Value = {$(=vMaxJose)}>} DateE),

                                      )))

                   

                  That may be a bit clunky for what you need especially if you have a lot of names but it should work to display the info you need.

                    • Max Function and respective Date on Chart

                      Thanks for your answer.I cannot go with this as there are more than 200 names in the table.Do you think any thing can be done thru script instead of chart?

                        • Re: Max Function and respective Date on Chart

                          Try somethin like this in your script:

                           

                          maxVal:

                          load Name, max(val) as MaxVal, lastvalue([start dt]) as LastStartDt, lastvalue([end dt]) as LastEndDt

                          resident YourDataTable group by Name;

                            • Max Function and respective Date on Chart

                              I used this and got it to work:

                               

                              Test:

                              LOAD * INLINE [

                              NameID, Name, Value, DateS, DateE

                              1, Bob, 400, 01/01/2011, 02/22/2020

                              1, Bob, 200, 01/03/2011, 02/25/2020

                              2, Joe, 250, 02/05/2012, 01/22/2000

                              2, Joe, 300, 02/15/2012, 02/18/2020

                              3, Jose, 400, 02/12/2014, 05/25/2025

                              3, Jose, 600, 01/01/2010, 03/22/2010

                              3, Jose, 100, 01/05/2012, 02/02/2018

                               

                               

                              ];

                               

                               

                              Test2:

                              LOAD

                                        Name,

                                        Max(Value) as MaxVal

                              Resident Test

                              GROUP BY Name;

                               

                               

                              Join(Test)

                              Load

                                        Name,

                                        MaxVal

                              Resident Test2;

                               

                               

                              DROP Table Test2;

                               

                               

                              Test3:

                              Load

                                        Name,

                                        Value as MaxValue,

                                        DateS as DateStart,

                                        DateE as DateEnd

                              Resident Test

                              WHERE Value          = MaxVal;

                               

                              Then use Name as your dimension and MaxValue, DateStart, and DateEnd as your expressions.