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

# 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

• ###### Re: Getting the first record of the year by person

Try 2 dimensions name (or Id ) and year, and expressions using  firstsortedvalue(), like   =firstsortedvalue({20"} >}SALES.Cost , SALES.Date )

• ###### Re: Getting the first record of the year by person

Sorry, my mobile ist killing me,  the set expression comes wrong, just use yours for  SALES.Age

• ###### Re: Getting the first record of the year by person

Thank you very much Swuehl,

That function worked perfectly.