topic Consecutive sales in QlikView

Consecutive sales

Sat, 17 Mar 2012 00:14:16 GMT

Hi All!

Is there a way to know for how many consecutive days I have been selling a product?

Let's say this is my table

date product value

13/03/2012 A 10

12/03/2012 A 12

10/03/2012 A 8

How can I make a table like:

product days sold in a row

A 2

Regards

Federico

Consecutive sales

Sat, 17 Mar 2012 21:13:53 GMT

Hi,

Try Somthung like:

LOAD * INLINE [

Date,product,value

13/03/2012,A,10

12/03/2012,A,12

10/03/2012,A,8

17/03/2012,D,12

18/03/2012,D,8

];

Left Join

LOAD

Date,

product,

if(IsNull(Previous(value)) OR product<>Previous(product),0,

if(product=Previous(product) and Date<> Previous(Date),Peek(Row)+1)) as Row

Resident a

Order by product,Date;

Final:

LOAD

product,

Max(Row) as days_sold_in_a_row

Resident a

Group by product;

Consecutive sales

Tue, 20 Mar 2012 16:11:52 GMT

This wont work... only counts the number of rows of each product -1.

Doesn't evaluate the date, for instance in product A there is a jump from 10 to 12/03.

I was looking more for an expression, otherwise I'll just build a store procedure for this.

Re: Consecutive sales

Tue, 20 Mar 2012 16:38:08 GMT

Hi ,

See the attached qvw,

Re: Consecutive sales

Tue, 20 Mar 2012 16:46:35 GMT

Hi Ygal.

If you check product D, it says 1 day in a row but the correct answer should be 2.

You are counting rows disregarding the dates.

A more appropiate function would be something like:

Left Join

LOAD

Date,

product,

if(IsNull(Previous(value)) OR product<>Previous(product),1,

if(product=Previous(product) and Date = Previous(Date) +1,Previous(Row)+1,1)) as Row

Resident a

Order by product,Date;

Even though, I wanted to know if someone could figure it out directly in a graph expression instead of the script.

Consecutive sales

Luis_Cortizo — Tue, 20 Mar 2012 18:40:30 GMT

I believe you could use something like this:

TMP:

LOAD * INLINE [

date, product, value

14/03/2012, A, 6

13/03/2012, A, 10

12/03/2012, A, 12

10/03/2012, A, 8

];

Table:

LOAD

If(InDay(peek('date', RowNo(),'TMP'), peek('date', RowNo()-1,'TMP'),-1), 1, 0) as Consecutive

Resident TMP

Order by date DESC;

Drop table TMP;

Which would return a table like this:

date	product	value	Consecutive
14/03/2012	A	6	1
13/03/2012	A	10	1
12/03/2012	A	12	0
10/03/2012	A	8	0

But you would have to code it in a way that looks for the same product as well, after that, its just to sum(Consecutive)+1 in a table using Product as Dimension.

I don't know if you would like to this in the script or in the design layer... to do it in the design layer, you can do basically the same thing using the above() or below() function.

Hope it helps.

Kind regards.

Consecutive sales

Luis_Cortizo — Tue, 20 Mar 2012 18:42:20 GMT

I should have read before submitting the answer...

Tomorrow I'll try to do the same thing using the above() function