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):
| Name | Id | Age | Date | Year | Month | Cost | Debt |
|---|
| Robert | 1 | 35 | 03-12-2012 | 2012 | March | 1000 | 200 |
| John | 2 | 22 | 06-04-2012 | 2012 | Jun | 3500 | 500 |
| Martha | 3 | 28 | 05-09-2012 | 2012 | May | 800 | 120 |
| Bob | 4 | 18 | 04-30-2012 | 2012 | Apr | 2840 | 350 |
| Martha | 3 | 27 | 01-15-2012 | 2012 | Jan | 1650 | 758 |
| Robert | 1 | 35 | 12-24-2012 | 2012 | Dec | 6580 | 4850 |
| Robert | 1 | 36 | 07-04-2013 | 2013 | Jul | 3240 | 1785 |
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) |
|---|
| Robert | 1 | 2012 | March | 1000 | 200 |
| John | 2 | 2012 | Jun | 3500 | 500 |
| Martha | 3 | 2012 | Jan | 1650 | 758 |
| Robert | 1 | 2013 | Jul | 3240 | 1785 |
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) |
|---|
| Robert | 1 | 2012 | March | 1000 | 200 |
| John | 2 | 2012 | Jun | 3500 | 500 |
| Martha | 3 | 0 |
| 0 | 0 |
| Robert | 1 | 2013 | Jul | 3240 | 1785 |
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
