Rolling averages with two criteria

[GoldenDesign04]

I have a data set that updates daily and contains 28 days of logs. It contains unique identifiers and concatenated call letters and frequency band. 

It has two columns End Date Events and Unique panelists that need a few averages calculated for each:

7 day average, last 4 day of week average

How would I structure a multi-row calculation to check if the call-ltr-band is the same and the media date to calculate these?

Attached is a sample. I looked at this: 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Get-the-7-day-trailing-avg-given-a-spe... but it uses a row count, not all the stations report all 7 or 28 days so using this assumption would result in miscounts

[jrgo]

Hi @GoldenDesign04 

See if the attached is what you're looking for. This only does the 7 day rolling average as I'm not 100% sure what exactly the other is supposed to represent. However, I'm thinking that the approach of this may give you some new ideas on how to approach.

Be sure to note this config option in the multi-row tool. Otherwise it will use 0's in the average for days that don't exist. setting as below will set the value to the nearest.

[mceleavey · Accepted solution]

Hi @GoldenDesign04 ,

If I understand this correctly then you're looking for the last 7 day average End Date Events and Unique Panelists for each Call ltr-Band ld. Is this correct?

I'm not sure what the last 4 day of week average means.

Anyway, I've attached the workflow for you:

This calculates those days within the last 7 days of today, then grouped by Call ltr-Band ld field and averaged the required columns:

If you also wanted the last 4 days, then you add another filter to the formula output and change the numbers accordingly, then join together on the Call ltr-Band ld field.

Hope this helps,

M.