Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi!
Take a look on the attached file. I think that is what you looking for.
Regards,
Gabriel
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
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
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
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
Thanks very much Gabriel, work great.
regards
Nicola