Calculate Start and End Dates

[#1]

Hi everyone. Consider the following table:

LOAD * INLINE [

    Date, CustomerID

    01/01/2013, 1

    01/01/2013, 2

    01/01/2013, 3

    01/01/2013, 4

    02/01/2013, 1

    02/01/2013, 2

    02/01/2013, 3

    02/01/2013, 5

    03/01/2013, 1

    03/01/2013, 2

    03/01/2013, 3

    03/01/2013, 5

    03/01/2013, 6

];

I would like to work out what the start and end dates are of each Customer. So for CustomerID 1 his start date would be 01/01/2013 and end date 03/01/2013 where as CustomerID 5 has a start date of 02/01/2013 and end date 03/01/2013 and so on. How can I calculate this?

Thank you.

[swuehl · Accepted solution]

Set DateFormat= 'MM/DD/YYYY';

INPUT:

LOAD * INLINE [

    Date, CustomerID

    01/01/2013, 1

    01/01/2013, 2

    01/01/2013, 3

    01/01/2013, 4

    02/01/2013, 1

    02/01/2013, 2

    02/01/2013, 3

    02/01/2013, 5

    03/01/2013, 1

    03/01/2013, 2

    03/01/2013, 3

    03/01/2013, 5

    03/01/2013, 6

];

LOAD Date(Min(Date)) as StartDate, Date(Max(Date)) as EndDate, CustomerID

RESIDENT INPUT

GROUP BY CustomerID;

DROP TABLE INPUT;

[Kushal_Chawda]

Data:

load Date,

         CustomerId

From table:

Left join(Data)

Load date(min(Date)) as StartDate,

          date(max(Date)) as EndDate,

          customerId

Resident Data

Group by CustomerId:

[Anonymous]

Hi ,

You can try in Expression as well

1)

aggr(max(date(Date2,'DD/MM/YYYY')),CustomerID)

2)

aggr(Min(date(Date2,'DD/MM/YYYY')),CustomerID)

[Kushal_Chawda]

On front end

Create straight table

Dimension:

CustomerId

Expression:

1) Start Date

     FirstSortedValue(Date, Date)

2) End Date

     FirstSortedValue(Date, -Date)

[swuehl]

Kushal,

why shouldn't we use Min(Date) and Max(Date) as expressions also on the front end side?

[Kushal_Chawda]

Yes we can use, do we have any advantage of min and max over firstsortedvalue

[swuehl]

Maybe only a couple:

- Max / Min could perform better, since there is no need to sort the Dates.

- No need to thinking about using DISTINCT qualifier in FirstSortedValue

- Potentially even get rid of an additional Date() format function when using Max / Min

[#9]

Thanks swuehl, this worked.