Count product Help

[Anonymous]

Hi

I need to count how many transactions have been made for each of the product from the every solddate + 7 days. This  is my table:

Product	SoldDate
1001	01-Jul-13
1001	03-Jul-13
1001	03-Jul-13
1001	04-Jul-13
1001	10-Jul-13
1002	04-Jul-13
1002	05-Jul-13
1002	06-Jul-13
1002	26-Jul-13

I wish to achieve the following result:

Product	SoldDate	Count
1001	01-Jul-13	4
1001	03-Jul-13	4
1001	03-Jul-13	3
1001	04-Jul-13	2
1001	10-Jul-13	1
1002	04-Jul-13	3
1002	05-Jul-13	2
1002	06-Jul-13	1
1002	26-Jul-13	1

For example, for product 1001, count for 01-Jul-13 is 4 because there are 4 transactions before 08-Jul-13 where solddate>=solddate but <=solddate+7.

Count for 04-Jul-13 is 2 because there are 2 transactions before 11-Jul-13.

It would be great if anyone can help me on this. Thanks in advance.

[Anonymous]

anyone can help me on this?

[Gysbert_Wassenaar · Accepted solution]

See attached example. I don't see a way to get two records for 3/7/2013 for product 1001 with different counts. Other than that it's what you want afaict.

[Anonymous]

Thanks Gysbert this is exactly what i want!!!

Could you explain to me the script? how does this "IterNo" function works?

And why i need to count(Amount)? instead of count(Product)?

[Gysbert_Wassenaar]

The iterno() is used to get the iteration number of the while loop. Your example didn't have a field named CIF. If you rather use another field then Amount to count that should work too as long as it isn't Product or SoldDate.

[Anonymous]

my mistake... what i mean is why we can't use Product? Count(Product)?