Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
See Attachment
what is the variable that you have taken?
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,
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))
//
//)))))
See new Attachment.
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
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,
hi this how my output is coming
Try this Expression
Count(DISTINCT If(Date >=StartDate and Date <= EndDate,Date))
No Antonio,
It gives just 1 on the months where the months of endate and start date have same month rest all are blanks