Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

count of previous rows in past 7 days - Multi-Row

steve87
7 - Meteor

Hi,

 

i have a workflow that should flag each date against same 'top most par calc' if appears in previous 7 days. That part is working fine in the workflow.

 

I would like to create new field which will calculate number of repetition in the previous 7 days from current date. On screenshot below you can see where the problem is

image.png

 

 

as you can see, calculation i made is not restarting count based on current date, but counting all previous records.

 

Workflow is attached

4 REPLIES 4
jdunkerley79
16 - Nebula
16 - Nebula

This is quite a fiddly problem.

 

If I am understanding it correctly I suggest the following.

 

Create a running total for each 'top most par calc'

You can then create a set of dates with a 7d ago and 180d ago.

Then create a complete running count for all these dates filling in the gaps

Finally join this series back to the data and you can produce the counts

 

I have attached a sample doing this

steve87
7 - Meteor

Oh it's a complicated workflow. @jdunkerley79 🙂

 

Unfortunately it is not giving me results that it should. I highlighted the wrong values

 

image.png

jdunkerley79
16 - Nebula
16 - Nebula

I had done within 7x 24 hours backward. I have adjusted it to be within the last 7 days. This is controlled by the formula tool date2_7d.

 

Have attached a new version for you to look at. Think matches required results

steve87
7 - Meteor

Thank you @jdunkerley79

 

This worked perfectly