topic Re: Showing Min + Max dates from an order date dimension in QlikView

Showing Min + Max dates from an order date dimension

Tue, 07 Oct 2014 12:34:41 GMT

Hi there, I was hoping someone would be able to help me, I'm fairly new to QV and don't have any coding experience and I have hit a problem with a straight table that may be pretty basic;

I have a chart and currently the I am showing all of the orders associate with the Visitor_ID no for example

Visitor_ID	Order_Date
232156	01/01/2009
232156	05/05/2010
232156	09/12/2011
232156	12/12/2012
123594	02/03/2010

What I need to show is this

Visitor_ID	First_Order_Date	Last_Order_Date
232156	01/01/2009	12/12/2012
123594	02/03/2010	02/03/2010

Is there anyway I can do this in a Chart using either an edited Dimension or an expression?

many thanks for your help

Nicola

Re: Showing Min + Max dates from an order date dimension

Anonymous — Tue, 07 Oct 2014 12:46:02 GMT

Hi!

Take a look on the attached file. I think that is what you looking for.

Regards,

Gabriel

Re: Showing Min + Max dates from an order date dimension

Tue, 07 Oct 2014 12:53:24 GMT

Hi Gabriel, thanks for the quick reply,

I only however have one field containing a date not two, I wanted to split out the first date associated with a customer and that last one.

regards

Nicola

Re: Showing Min + Max dates from an order date dimension

Anonymous — Tue, 07 Oct 2014 12:58:45 GMT

Hello Nicola.

Take a look on the script of the qvw that I've attached before.

I've created an excel file with this structure:

Visitor_ID	Order_Date
232156	01/01/2009
232156	05/05/2010
232156	09/12/2011
232156	12/12/2012
123594	02/03/2010

In the LOAD I do this:

LOAD Visitor_ID,

date(min(Order_Date)) as Min_Date,

date(max(Order_Date)) as Max_Date

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1)

Group by Visitor_ID;

So, this way I get the min and the max dates in separated fields, grouping by Visitor ID.

Regards,

Gabriel

Re: Showing Min + Max dates from an order date dimension

Tue, 07 Oct 2014 13:02:25 GMT

OK thanks Gabriel, i'd hoped that there was a way to do it without having to make any changes to the script.

thanks again

regards

Nicola

Re: Re: Showing Min + Max dates from an order date dimension

Anonymous — Tue, 07 Oct 2014 13:07:44 GMT

It's also possible to make it without changes in the script.

Take a look on the attached file.

There i've created expressions on the table with min(Order_Date) and max(Order_Date).

Regards,

Gabriel

Re: Showing Min + Max dates from an order date dimension

Tue, 07 Oct 2014 13:13:55 GMT

Thanks very much Gabriel, work great.

regards

Nicola