Consecutive sales

[#1]

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

[#2]

Hi,

Try Somthung like:

a:

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;

[#3]

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.

[#4]

Hi ,

See the attached qvw,

[#5]

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.

[Luis_Cortizo]

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.

[Luis_Cortizo]

I should have read before submitting the answer...

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