10 Replies Latest reply: May 19, 2015 6:30 AM by Kumar Pramod RSS

    Highest count in a week

    Kumar Pramod

      Hi all,

       

      i have a two tables coursecompletion and signup activitylog.

       

      coursecompletion table:                                     

      course_completed                    Date

      0                                      10/05/2015

      1                                       10/05/2015

      0                                      10/05/2015

      1                                       10/05/2015

      1                                       11/05/2015

      1                                       11/05/2015

      1                                        11/05/2015

      0                                        11/05/2015

      1                                        12/05/2015

      1                                   12/05/2015
      112/05/2015
      112/05/2015
      112/05/2015
      112/05/2015
      112/05/2015

       

       

      signupactivitylog Table:

      user_signed                      Date

      0                                      10/05/2015

      1                                       10/05/2015

      0                                      10/05/2015

      1                                       10/05/2015

      1                                       11/05/2015

      1                                       11/05/2015

      1                                        11/05/2015

      0                                        11/05/2015

      1                                        12/05/2015

      1                                   12/05/2015
      112/05/2015
      112/05/2015
      112/05/2015
      112/05/2015
      112/05/2015

       

       

      from the above two tables i need in which day count of the user_signed and course_completed is more.

       

      Thanks,

      Pramod

        • Re: Highest count in a week
          jagan mohan rao appala

          Hi,

           

          Try like this

           

          coursecompletiontable:

          LOAD

          course_completed,

          Date(Date#(Date, 'MM/DD/YYYY')) AS Date

          INLINE [                                   

          course_completed,                    Date

          0,                                      10/05/2015

          1,                                       10/05/2015

          0 ,                                     10/05/2015

          1,                                       10/05/2015

          1,                                       11/05/2015

          1,                                       11/05/2015

          1,                                        11/05/2015

          0,                                        11/05/2015

          1,                                        12/05/2015

          1,                                   12/05/2015

          1, 12/05/2015

          1, 12/05/2015

          1, 12/05/2015

          1, 12/05/2015

          1, 12/05/2015];

           

           

          For getting maximum course_completed  date use below expression

          =Date(FirstSortedValue(Date, -Aggr(Sum(course_completed), Date)))


          The same way do this for User signed.


          Regards,

          jagan.