I am trying to create a chart that gives me cumulative value for rolling 6 months based on the date selected.
My data has 4 types of status dates at Application level (There are also many more columns in the dataset). For example, an application can move from Status 1 to Status 2 to Status 3 to Status 4 on different dates. I want to add a common running date filter based on which the number of applications should be filtered (because neither of the 4 columns have exhaustive list of dates). For this, I imported another data file containing running date for a period of about 3 years and used it as filter (full_date).
I have also extracted the day out of each date as day_s1date, day_s2date, etc.
Table1:
| ApplicationID | S1_date | S2_date | S3_date | S4_date |
| 1 | 07/09/2018 | 10/09/2018 | 11/09/2018 | 17/09/2018 |
| 2 | 06/11/2018 | 08/11/2018 | | |
| 3 | 10/11/2018 | | | |
| 4 | 20/06/2018 | | | |
Table2:
| Full_date |
| 01/01/2017 |
| 02/01/2017 |
| 03/01/2017 |
| ... |
| ... |
| ... |
| 31/12/2019 |
Based on the date selected, the output should show cumulative count of applications from month start date till that date for rolling 6 months. If I select 15/11/2018 from the filter, I need count from 1 to 15 for June'18 to Nov'18.
| Cumulative month | S1 count | S2 count | S3 count | S4 count |
| Jun'18 | | | | |
| Jul'18 | | | | |
| Aug'18 | | | | |
| Sep'18 | 1 | 1 | 1 | |
| Oct'18 | | | | |
| Nov'18 | 2 | 1 | | |
I have used this for Dimension =(Date(monthstart(full_date), 'MMM-YY'))
I have used this for Measure
S4 count:
count({<
S4_date={">=$(=date(Max(full_date)-180,'DD/MM/YYYY'))<=$(=max(full_date))"},
Day_s4date={"<=$(=Day(Max(full_date)))"}
>}DISTINCT (APPLICATIONID))
S3 count
count({<
PRODUCT_LEAP={'HE'},
S3_date={">=$(=date(Max(full_date)-180,'DD/MM/YYYY'))<=$(=max(full_date))"},
Day_s3date={"<=$(=Day(Max(full_date)))"}
>}DISTINCT (APPLICATIONID))
But this is not giving me the split at month level. I am getting only the total value for the 6 months like:
| Cumulative month | S1 count | S2 count | S3 count | S4 count |
| Nov'18 | 3 | 2 | 1 | |
Can someone help me how to get the month-wise count?
