topic Re: Create autonumber column ordered by sales date grouped by username in QlikView

Create autonumber column ordered by sales date grouped by username

Mon, 04 Feb 2013 12:42:47 GMT

Hello,

I have sales table that have usernames and sales_dates. I want to give sales number
for every sales ordered by date for every user. Then I would like to create a column
that shows the days between sales.
Black columns are original data. I would like to produce red data. How can I do that in qlikview?

Username	Sales_date	Sales_no	Date_difference
john	14.01.2013	1
john	17.01.2012	2	3
john	25.01.2013	3	8
Wade	10.01.2013	1
Wade	18.01.2013	2	8
Wade	31.01.2013	3	13

FUID	Sales_date	Sales_no	Date_diffreence
John	15.01.2012	1
John	17.01.2012	2	2
John	23.01.2012	3	6
Wade	16.01.2012	1
Wade	25.02.2012	2	40
Wade	03.03.2012	3	7
Fred	04.04.2012	1
Fred	09.04.2012	2	5
Fred	24.04.2012	3	15

FUID	Sales_date	Sales_no	Date_diffreence
John	15.01.2012	1
John	17.01.2012	2	2
John	23.01.2012	3	6
Wade	16.01.2012	1
Wade	25.02.2012	2	40
Wade	03.03.2012	3	7
Fred	04.04.2012	1
Fred	09.04.2012	2	5
Fred	24.04.2012	3	15

FUID	Sales_date	Sales_no	Date_diffreence
John	15.01.2012	1
John	17.01.2012	2	2
John	23.01.2012	3	6
Wade	16.01.2012	1
Wade	25.02.2012	2	40
Wade	03.03.2012	3	7
Fred	04.04.2012	1
Fred	09.04.2012	2	5
Fred	24.04.2012	3	15

FUID
Sales_date
Sales_no
Date_diffreence

John
15.01.2012
1

John
17.01.2012
2
2

John
23.01.2012
3
6

Wade
16.01.2012
1

Wade
25.02.2012
2
40

Wade
03.03.2012
3
7

Fred
04.04.2012
1

Fred
09.04.2012
2
5

Fred
24.04.2012
3
15

Re: Create autonumber column ordered by sales date grouped by username

Mon, 04 Feb 2013 13:15:56 GMT

is Your data set ordered by date?

try like this:

LOAD Username,

Sales_date,

if (recno()=1,1,if(Previous(Username)=Username,Previous(Sales_no)+1,1)) as Sales_no,

if(Previous(Username)=Username, Sales_date-Previous(Sales_date),'') as Date_difference

FROM

(ooxml, embedded labels, table is Data_sample);

Re: Create autonumber column ordered by sales date grouped by username

Mon, 04 Feb 2013 14:57:04 GMT

Thanks.

My dataset was not ordered. I have ordered it by username,date. But it gives error. Becuse, there is no column called sales_no . If I comment sales_no, Date_difference works fine.

Field not found - <Sales_no>

LOAD username,

sales_date,

if (recno()=1,1,if(Previous(username)=username,Previous(Sales_no)+1,1)) as Sales_no,

if(Previous(username)=username, sales_date-Previous(sales_date),'') as Date_difference

FROM

[DATA.QVD](qvd)