Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
this is the data i have
1/31/2017 | 2/28/2017 | 3/31/2017 | 4/30/2017 | 5/31/2017 | 6/30/2017 | 7/31/2017 | 8/31/2017 | 9/30/2017 | 10/31/2017 | 11/30/2017 | 12/31/2017 | |||
ID | Start Date | End Date | 1/1/2017 | 2/1/2017 | 3/1/2017 | 4/1/2017 | 5/1/2017 | 6/1/2017 | 7/1/2017 | 8/1/2017 | 9/1/2017 | 10/1/2017 | 11/1/2017 | 12/1/2017 |
A | 1-Jan-17 | 15-Mar-17 | 31 | 28 | 17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
B | 5-Jun-17 | 4-Aug-17 | 0 | 0 | 0 | 0 | 0 | 26 | 31 | 28 | 0 | 0 | 0 | 0 |
C | 8-Aug-17 | 4-Nov-17 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 24 | 30 | 31 | 27 | 0 |
now in this data suppose the ID A start date is 1-Jan-2017
and end date is 15-Mar-2017
this means user A achieve policy on this specific time period so i want total days of these specific months
ie. from Jan they have 31 days then in Feb 28 days and in march 17 days .. because for jan
he have start date 1-Jan-2017 and end date for jan is 31-Jan-2017 so here (1-31-2017)-(1-Jan-2017) +1
for Feb we have 2/28/2017 - 2/1/2017 +1
for march we have 3/31/2017-(15-March-201 )+1
In jan and feb they achieve policy full month but in march policy end in 15-March-2017 so they have remaining 17 days
now how i achieve this dates formula through qlikview expression ? any idea ?
i attached qlik file
any help please