Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

surynnchin
New Contributor III

Count product Help

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:

ProductSoldDate
100101-Jul-13
100103-Jul-13
100103-Jul-13
100104-Jul-13
100110-Jul-13
100204-Jul-13
100205-Jul-13
100206-Jul-13
100226-Jul-13

I wish to achieve the following result:

ProductSoldDateCount
100101-Jul-134
100103-Jul-134
100103-Jul-133
100104-Jul-132
100110-Jul-131
100204-Jul-133
100205-Jul-132
100206-Jul-131
100226-Jul-131

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.

1 Solution

Accepted Solutions
Highlighted

Re: Count product Help

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.


talk is cheap, supply exceeds demand
5 Replies
surynnchin
New Contributor III

Re: Count product Help

anyone can help me on this?

Highlighted

Re: Count product Help

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.


talk is cheap, supply exceeds demand
surynnchin
New Contributor III

Re: Count product Help

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

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.


talk is cheap, supply exceeds demand
surynnchin
New Contributor III

Re: Count product Help

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

Community Browser