topic Re: Count product Help in QlikView

Count product Help

Anonymous — Tue, 13 Aug 2013 03:10:31 GMT

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.

Re: Count product Help

Anonymous — Tue, 13 Aug 2013 16:13:06 GMT

anyone can help me on this?

Re: Count product Help

Gysbert_Wassenaar — Tue, 13 Aug 2013 16:29:20 GMT

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.

Re: Count product Help

Anonymous — Tue, 13 Aug 2013 16:53:47 GMT

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)?

Re: Count product Help

Gysbert_Wassenaar — Tue, 13 Aug 2013 17:06:11 GMT

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.

Re: Count product Help

Anonymous — Tue, 13 Aug 2013 17:37:28 GMT

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