Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Gysbert_Wassenaar

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

View solution in original post

5 Replies
Anonymous
Not applicable
Author

anyone can help me on this?

Gysbert_Wassenaar

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
Anonymous
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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