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
