Hi,
I run some reports that come out monthly. Each report has a Month (Date) field that will be in the format 1/11/2024, but displayed as Nov-24.
Each of these reports are saved in a directory as part of the workflow, so we have a library of aged files.
I need to be able to track if a line appears in a month, then if it appears in the second month, and then again in the third month. If a line skips a month then the tracking resets.
Each report that has a few fields :
| PO Number | PO Line Number | Dist | PNumber | Month (Date) |
| 1234 | 1 | 1 | 1X123XT | 1/11/24 |
| 1234 | 2 | 1 | 1X123XT | 1/11/24 |
| 1235 | 1 | - | - | 1/11/24 |
| 1235 | 1 | - | - | 1/11/24 |
| 1234 | 1 | 1 | 1X123XT | 1/12/24 |
| 1235 | 1 | - | | 1/12/25 |
| 1236 | 1 | 1 | - | 1/01/25 |
| 1237 | 1 | - | - | 1/01/25 |
The report can have duplicated lines for the first two columns and month, and no data in Dist and PNumber. The lines always have PO Number + PO Line Number + Month
My current workflow prepares the current month report, as well at the same time reads in an output folder for the previous two months files, and merges all three months data together.
So far, to enable me to track the lines over 3 months I have
- the output above (3 merged files) going into a formula tool to create a 'Concat' field and combine the first 4 fields.
- Then a sort on the Month (Date) column from newest month to oldest
This goes into a multi row formula.
Create New Field - OccurenceCount
Formula :
IF ISNULL([Row-1:PO Number])
OR ([Concat] != [Row-1:Concat])
AND [Month(Date)] != [Row-1:Month(Date)] THEN
1
ELSE
[Row-1:OccurenceCount] + 1
ENDIF
Everything I have tried has not worked.
What I think I need is the output with these fields, I just cant get it to work no matter what I try:
MonthGap - to calculate the gap between consecutive months for the same Concat lines
ResetFlag - to flag when a consecutive monthly reset should occur
AppearanceCounter - a sequential counter that resets on gaps
AppearanceLabel - to create descriptive labels for the appearance counter: First appearance, Second appearance, Third appearance
Any help would be appreciated.
