Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

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
ACE Emeritus
ACE Emeritus

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
ACE Emeritus
ACE Emeritus

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