Count the active days, grouped by user, year and month

[#1]

Hi, i need to count the active days (the interval of EndDate and InitialDate) and group them by User, Year and Month. If the EndDate is null then it means is still active so it must use the last day of the active month (actually 31 december)

InitialDate is => 01/07/2011

User	InitialDate	EndDate
1	01/07/2011	10/09/2011
2	01/07/2011
3	05/07/2011	11/10/2011
4	10/08/2011	20/12/2011
5	10/08/2011
6	08/11/2011	09/11/2011
7	08/11/2011

The expected data is:

User	Jul	Ago	Sep	Oct	Nov	Dec
1	31	31	10	0	0	0
2	31	31	30	31	30	31
3	27	31	30	11	0	0
4	0	22	30	31	30	20
5	0	22	30	31	30	31
6	0	0	0	0	2	0
7	0	0	0	0	23	31

Hope you can help me.

Regards

[lironbaram · Accepted solution]

hei

have a look at this example

i used intreval match for the calculation

[rohit214]

hi

try this

to count the active days

=count(if(len(enddate)=0 or isnull(enddate)='-1',enddate))

then in script write

load

month(field) as month,

year(field) as year,

user

from xyz.qvd group by user,year;

use pivot table choose

dimension user,month

streach month column and you will get your ans

thanks

rohit

[#4]

Great!! I have to do some changes but it's doing what i need.

Thanks!