Data Trending

Paul Newcome

Glad you were able to get it sorted.

Happy to help. 👍️

Mike Arbaugh

Paul

I wanted to reach out again. I been using that formula you gave me. An issue I been running into is that it is not picking up when the product name changes. If you look at the picture, it shows 19 for both product names. It should change when the product name changes and the number should increase to 20. I have my row I set up and it is counting. Can you see what I am doing wrong?

Paul Newcome

Can you provide a screenshot showing the Product Name, Count, and Row ID columns lined up? You can temporarily hide the unnecessary columns if needed to get them into the same screenshot.

Mike Arbaugh

Here you go. When you see the first 1 on the left, it has #14 beside it and the product name of Almond Unsweet Original. Then it doesn't change when we go to the Almond Coconut Unswt. I didnt start seeing these issues until the row ID got to a high number.

Paul Newcome

Have you done a filter on the sheet to only show rows with "Almond Coconut Unswt" to see if there are any other rows in the sheet for that product?

Mike Arbaugh

The same product could show up within the last 10 days.

Mike Arbaugh

I archived the data within 7 days so I would not have the same name show up again and that seems to have fixed it. I thought it would look at the name and the highest number also.

Paul Newcome

I don't remember seeing anything about that previously. My apologies. Glad you were able to get it sorted.

Mike Arbaugh

Well I thought that fixed it,, but now it is still doing it. So, what could be causing it not to pick up the different names? Do you have another way of fixing it.

Mike Arbaugh

Paul

I been playing around with it a little more. What it looks like it is doing is it looks at the string of names. So, If I have the same name show up a few days ago and then the same name start again today, it throws the formula off and starts acting up. Below is the formula. Is it possible to also have it look at the current date? This way it can look at the string of names and only reference the ones with the current date beside them. Or if you have any other ideas.

=COUNT(DISTINCT(COLLECT([Product Name]:[Product Name], [Row ID]:[Row ID], <=[Row ID]@row)))

Paul Newcome

You can add another range/criteria set looking at the date column (range) and being equal to the date column "@row" (criteria).

Mike Arbaugh

Can you give me an example of what that would look like in that formula below if I referenced a date row also.

=COUNT(DISTINCT(COLLECT([Product Name]:[Product Name], [Row ID]:[Row ID], <=[Row ID]@row)))

Paul Newcome

.....[Column Name]:[Column Name, @cell = [Column Name]@row.....

Mike Arbaugh

Paul

This is what I put in and it isnt working correct.

=COUNT(DISTINCT(COLLECT([Product Name]:[Product Name], [Row ID]:[Row ID], <=[Row ID]@row, Date:Date, Date@row = Date@row)))

Paul Newcome

Date@row = Date@row

should be

@cell = Date@row