Hii
Looking for simpler solution in Alteryx designer for creating 2 new columns from the input data rows.
In my attached excel file, Columns A to F will be the input data which consists of thousands of rows for different group, location, metrics and Date months.
I have also included output columns G and H in this input file just for reference to show how i want them to be calculated
is there efficient and simple alteryx logic that anyone can help me with here? to calculate these 2 output columns (Column G and H). May be someone can share screenshots of logic or required component configs here
Column G logic=
(For each metric and Location ID (If location ID is null then for Group ID), i need to get consecutive month trigger count scanning the file in descending month order )
Example 1:
For Metric= acceptance, Location ID = 100 , i need to calculate for each month row, i need to go back to consecutive previous date and see if it is present and count how many present
So Data row 1: Date= 2025-12. i See there is 1 consecutive month (2025-11) is present for Acceptance metric and Location id 100, So for this row 1, column G value should be 1
Data row 2: Date= 2025-11, i dont find consecutive previous month for same metric and location id (month 2025-10 is not present). so here Column G is 0
Example 2:
For metric= 'acceptance', when location id is absent, then i need to calculate for Acceptance metric for ex. for group id= 2, for each month row, i need to go back to consecutive previous date and see if it is present.
So Data row 8: Date= 2025-09. i see there are 2 consecutive previous months present for same metric and group id (2025-08, 2025-07 both present) , so here value of column G = 2
Column H logic=
with same approach i need to calculate this for each row at metric and location id level (if location id is null then at group id level).
Here i need to count total occurances in last 12 month window for each row
example 1:
Data row 1: Date = 2025-12. For Location id 100 and metric=acceptance, i need to calculate occurances from 2025-01. I see 4 occurances (2025-12, 2025-11,2025-09,2025-07). So for row 1, value of column H will be 4.
example:2
Data row 19: Date = 2025-10. There is no previous date row for metric=acceptance and group id=5. only current row. So value here for column H = 1
Same logic for 'Stale' metric rows
