topic Re: Calculate Start and End Dates in QlikView

Calculate Start and End Dates

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Calculate Start and End Dates

swuehl — Thu, 19 May 2016 12:25:37 GMT

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;

Re: Calculate Start and End Dates

Kushal_Chawda — Thu, 19 May 2016 12:32:11 GMT

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:

Re: Calculate Start and End Dates

Anonymous — Thu, 19 May 2016 12:35:15 GMT

Hi ,

You can try in Expression as well

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

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

Re: Calculate Start and End Dates

Kushal_Chawda — Thu, 19 May 2016 12:38:51 GMT

On front end

Create straight table

Dimension:

CustomerId

Expression:

1) Start Date

FirstSortedValue(Date, Date)

2) End Date

FirstSortedValue(Date, -Date)

Re: Calculate Start and End Dates

swuehl — Thu, 19 May 2016 12:44:30 GMT

Kushal,

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

Re: Calculate Start and End Dates

Kushal_Chawda — Thu, 19 May 2016 12:53:34 GMT

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

Re: Calculate Start and End Dates

swuehl — Thu, 19 May 2016 12:58:58 GMT

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

Re: Calculate Start and End Dates

Thu, 19 May 2016 19:37:05 GMT

Thanks swuehl, this worked.