TwinJohnson
New Member
- Joined
- Jul 15, 2021
- Messages
- 11
- Office Version
- 365
- 2011
- Platform
- Windows
Hello,
I have a file with various date of events within each category that the entry goes through. Ex) File entered 10/07/22, the file is pulled for review 10/09/22, but the same file is not completed until 10/11/22 all the while new files are coming in and moving across the buckets daily.
To add up the number of files in any given day not by the entry date, completed date or review date, I therefore created a date table (calendar auto) and added four calculated columns to the data table to calculate the # of instances per date within the buckets that have occurred each day using the formula ex) File entered but not reviewed = COUNTROWS (FILTER (TABLE NAME [COLUMN NAME]= MAX ('Date'(DATE TABLE]))) which gives no data and SUM or SUMX doesn't work.
In the master table I added another calculated column that places a # 1 when the file reaches a certain stage. Stage = PER Closed, PER OPEN NOT REVIEWD, IN REVIEW NOT CLOSED in order to count the instances in the above calculation within the data table..
I have a file with various date of events within each category that the entry goes through. Ex) File entered 10/07/22, the file is pulled for review 10/09/22, but the same file is not completed until 10/11/22 all the while new files are coming in and moving across the buckets daily.
To add up the number of files in any given day not by the entry date, completed date or review date, I therefore created a date table (calendar auto) and added four calculated columns to the data table to calculate the # of instances per date within the buckets that have occurred each day using the formula ex) File entered but not reviewed = COUNTROWS (FILTER (TABLE NAME [COLUMN NAME]= MAX ('Date'(DATE TABLE]))) which gives no data and SUM or SUMX doesn't work.
In the master table I added another calculated column that places a # 1 when the file reaches a certain stage. Stage = PER Closed, PER OPEN NOT REVIEWD, IN REVIEW NOT CLOSED in order to count the instances in the above calculation within the data table..
Date Table | Total File entered | # of Total files Not Reviewed | # of total files entered that have been pulled for review but Not Closed | # of total files entered that have been pulled and are now completed |
1/18/2022 | 5 ex) this says 5 files were entered on 01/18/2022 | 2 | 2 | 1 |
1/19/2022 | 10 ex) 10 files were entered on 01/19/2022 | 12 ex) of the 15 total files entered to date, 12 have not been reviewed | 1 ex) of the 15 total files entered to date, 12 have not been reviewed but 3 have (2 from previous day and 1 today) | 2 ex) of the 15 total files entered to date, 12 have not been reviewed, 3 are in review and 3 have been completed |
1/20/2022 | 15 | 27 | 3 | 7 |
1/21/2022 | 20 | 37 | 9 | 0 |
1/22/2022 | 25 | 25 | 0 | 0 |
Attachments
Last edited:
