3 Replies Latest reply: Feb 20, 2013 9:13 AM by Ryuma Nakano RSS

    Getting the first record of the year by person

      Hi,

       

      I am trying to get the first record of the year by each person in a simple table object from a single data source table.

       

      Data Source Table (SALES):

       

      NameIdAgeDate Year MonthCostDebt
      Robert13503-12-20122012March1000200
      John22206-04-20122012Jun3500500
      Martha32805-09-20122012May800120
      Bob41804-30-20122012Apr2840350
      Martha32701-15-20122012Jan1650758
      Robert13512-24-20122012Dec65804850
      Robert13607-04-20132013Jul32401785

       

       

      I need to get the first record of each person in a year, and the person needs to be at least 20 years old:

       

       

      Name (Dimension)Id (Dimension)Year (Expression)Month (Expression)Cost (Expression) Debt (Expression)
      Robert12012March1000200
      John22012Jun3500500
      Martha32012Jan1650758
      Robert12013Jul32401785

       

      Here is the expression I am using in the alphanumeric columns, in this case I use the "Month" Expression:

       

      concat

      (

                {<

                          SALES.Age ={'>=20'}                   

                >}distinct

                if

                (

                          SALES.Date =

                          Aggr

                          (

                          min

                                    (

                                              {1<

                                                        SALES.Age ={'>=20'}    

                               , SALES.Year = {$(= concat( distinct SALES.Year, ','))}                  

                                              >}

                                              SALES.Date

                                    )

                                    ,SALES.Id

                                    ,SALES.Year

                          )

                          ,SALES.Month

                )

      )

       

      For numeric expressions I use:

       

      sum

      (

           {<

               SALES.Age ={'>=20'}                   

           >}distinct

           if

           (

               SALES.Date =

               Aggr

               (

               min

                     (

                         {1<

                               SALES.Age ={'>=20'}    

                               , SALES.Year = {$(= concat( distinct SALES.Year, ','))}                  

                         >}

                         SALES.Date

                     )

                     ,SALES.Id

                     ,SALES.Year

               )

               ,SALES.Cost

           )

      )

       

      Now, the problem I have right now is that the expressions work most of the time but there are a few cases where they do not and I do not know why. This is what I am getting:

       

      Name (Dimension)Id (Dimension)Year (Expression)Month (Expression)Cost (Expression) Debt (Expression)
      Robert12012March1000200
      John22012Jun3500500
      Martha30
      00
      Robert12013Jul32401785

       

      I would like to know if I am doing the expressions wrong or if there is any better way to do what I need.

       

      Thank you for taking the time to read this and I appreciate any help.

       

      - Rnakano