Calculations with set analysis in Pivot table

vpanchuda — Tue, 11 Jun 2019 11:23:38 GMT

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

topic Calculations with set analysis in Pivot table in App Development

Calculations with set analysis in Pivot table