Count most recent consecutive number of days

[jhwth]

I have an input similar to below:

Dates	Product A	Product B	Product C	Product D
2022-03-01	0	100	100	0
2022-03-02	100	100	0	0
2022-03-03	100	0	0	0
2022-03-04	200	0	0	0
2022-03-05	200	200	0	0
2022-03-06	200	200	0	0
2022-03-07	200	200	0	100

My goal is to count the most recent # of consecutive dates from my latest day (which in this case is March 7th) backwards where the value is > 0. So I expect my answer to be:

Product A	6
Product B	3
Product C	0
Product D	1

Is there a way to do this in Alteryx? I've been playing with the formula tool but unsuccesful so far.

[FinnCharlton]

Hi @jhwth , this workflow should work for you. I sort the data based on the dates, and then use a Multi-Row Formula tool to calculate the number of consecutive days since the latest day in the dataset. The formula isn't the cleanest and I'm sure there's a nicer way to do it, but this does the job.

[BS_THE_ANALYST]

@jhwth I added some extra data in to test if the logic holds up. Seems good: