Distributing Days across Months

[rahulsingh12]

Hi,

I have two dates

start date: 01/05/2017    

EndDate: 03/06/2017

I want to distribute the number of days between the days to the months.

start date: 01/05/2017     jan   feb   mar   apr  may  jun   jul  aug sep oct   nov   dec

EndDate: 03/06/2017        0      0     0       0      31    3      0     0    0    0      0       0

How can I achieve this?

[antoniotiman]

See Attachment

[rahulsingh12]

what is the variable that you have taken?

[rahulsingh12]

i have a Common date from which I will take the months across which I will distribute the data

so with this expression

Sum(If([common date]>=$(=Min(StartDate)) and [common date]<= $(=Max(EndDate)),1,0))

And also I will have 10k such values for start date and end date for each of the combination I would want to distribute the number.

But with the above expression I am not getting anything in the chart.

regards,

[rahulsingh12]

I had tried nested if but seems to have some error

//IF(LinkMonth>=Month(ADVOCACY_FROM),

//IF(Month(ADVOCACY_TO)>=LinkMonth AND Year(ADVOCACY_TO)=LinkYear,

//IF(Month(ADVOCACY_FROM)=MONTH(ADVOCACY_TO) AND Year(ADVOCACY_FROM)=Year(ADVOCACY_TO) and day(ADVOCACY_TO)<>day(ADVOCACY_FROM),

//Interval(((ADVOCACY_TO)-ADVOCACY_FROM),'DD')+1,

//IF(Month(ADVOCACY_FROM)=MONTH(ADVOCACY_TO) AND Year(ADVOCACY_FROM)=Year(ADVOCACY_TO) and day(ADVOCACY_TO)=day(ADVOCACY_FROM),1,

//IF(LinkMonth=Month(ADVOCACY_FROM) AND Year(ADVOCACY_FROM)=LinkYear,

//Interval((MonthEnd(ADVOCACY_FROM)-ADVOCACY_FROM),'DD'),

//IF(LinkMonth=Month(ADVOCACY_TO) AND Year(ADVOCACY_TO)=LinkYear,

//Interval((MonthStart(ADVOCACY_TO)-ADVOCACY_TO),'DD'),

//IF(Date(MonthStart(ADVOCACY_FROM),'MMM-YYYY')<Date(MonthStart(LinkDate),'MMM-YYYY')

//AND Date(MonthStart(LinkDate),'MMM-YYYY')<Date(MonthStart(ADVOCACY_TO),'MMM-YYYY'),

//Floor(MonthEnd(LinkDate)) - Floor(MonthStart(LinkDate)) + 1))

//

//)))))

[antoniotiman · Accepted solution]

See new Attachment.

[effinty2112]

Hi Rahul,

Use a calendar in your script. If you don't have one try something like:

Let vFirstDay =num(Date('01/01/2017'));

Let vLastDay = num(Date('31/12/2017'));

Calendar:

LOAD 

    Month(Date) As Month,

    Date;

Load Date($(vFirstDay) + RecNo() -1 ) AS Date

AutoGenerate $(vLastDay) - $(vFirstDay) +1;

Now this expression in a straight table should work for you:

Count({$<Date = {">=$(vStartDate)<=$(vEndDate)"}>}Date)

where  vStartDate and vEndDate are your own variables

Month	Count({$<Date = {">=01/05/2017<=03/06/2017 "}>}Date)
	34
May	31
Jun	3

or you can add an extra expression Count(Date) and in the Presentation tab set that new column to hidden to get:

Month	Count({$<Date = {">=01/05/2017<=03/06/2017 "}>}Date)
	34
Jan	0
Feb	0
Mar	0
Apr	0
May	31
Jun	3
Jul	0
Aug	0
Sep	0
Oct	0
Nov	0
Dec	0

Cheers

Andrew

[rahulsingh12]

Hi Andrew,

thanks for the reply.

But my first date and last date comes from a database table which has million of first date and last date

So I need some expression in the front end which could use the database fields and give the output.

Regards,

[rahulsingh12]

hi this how my output is coming

[antoniotiman]

Try this Expression

Count(DISTINCT If(Date >=StartDate and Date <= EndDate,Date))

[rahulsingh12]

No Antonio,

It gives just 1 on the months where the months of endate and start date have same month rest all are blanks