HI I would like to build output in a Pivot Table for the below data, tried a lot and messed up it would be a great help if someone helps me out to calculate and get the below output with set expressions.'
INPUT TABLE
| ID | Reference | Status | Duedate |
| 1 | A1 | YES | |
| 2 | B1 | NO | 11-07-2019 |
| 3 | C1 | NO | 12-07-2019 |
| 4 | A1 | NO | 13-07-2019 |
| 5 | B1 | NO | 17-08-2019 |
| 6 | C1 | YES | |
| 7 | C1 | NO | 16-08-2019 |
| 8 | B1 | YES | |
| 9 | A1 | NO | 18-09-2019 |
| 10 | A1 | NO | 18-10-2019 |
Desired OUT PUT table should look like below.
1- the count as per today.
2- TOTAL YES for all Reference(A1,B1,C1) --This adds up the count of NO to YES on the months where its converted on the respective month/date.
| Count of Status wrt Weight | Today(Count as per today) | 30-07-2019(end of next month) | 30-08-2019(+30 days from Previous month) | 30-09-2019(+30 days from Previous month) | 30-10-2019(+30 days from Previous month) | 30-07-2019(+30 days from Previous month) |
| TOTAL YES for all Reference(A1,B1,C1) | 3 | 6 | 8 | 9 | 10 | |
| Missing Due Date | 2 | 2 | 2 | 2 | 2 | 2 |
| A1-NO | 3 | 2 | 2 | 1 | 0 | |
| B1-NO | 2 | 1 | 0 | 0 | 0 | |
| C1-NO | 2 | 1 | 0 | 0 | 0 | |
| TOTAL number of No conversions reduced every month | 7 | 4 | 2 | 1 | 0 | |
