Is the number (55 in your example) linked to number of days?
i.e. is 17+30+2 meant to add to 55? or are they exclusive?
Hard to tell what you need without a sample workbook. Provided you have another field used to display months - [Day] in my solution - you may use the following to calculate the number of days shared during the month:
IF YEAR([date1])*100+MONTH([date1]) <= YEAR(MIN([Day]))*100+MONTH(MIN([Day])) AND YEAR([date2])*100+MONTH([date2]) >= YEAR(MIN([Day]))*100+MONTH(MIN([Day])) THEN datediff('day', IIF([date1]<MIN([Day]), MIN([Day]), [date1]), IIF([date2]<dateadd('month', 1, MIN([Day])), dateadd('day',1,[date2]), dateadd('month', 1, MIN([Day])))) ELSE 0 END
the results look like this:
55 is not meant to add to the number of days between the initial and end dates. However, how those 55 are distributed depends on the number of days between both dates.
If we were to split the number 55 by month, and the first date started in January 15th 2015, and ended on March 20th 2015, so there 65 days between both dates (instead of the 47 days in the first example). The distribution would become like this:
- January has 17 of the 65 days in the date interval (or 26%) -- 55 * 0.26 = 14.3
- February has 28 days in the interval (or 43%) -- 55 * 0.43 = 23.65
- March has 20 days (31%) -- 55 * 0.31 = 17.05
The results would then end up in a bar chart with the 3 months displayed with the corresponding values (14.3, 23.65 and 17.05)
I wonder if attached is something you can use?
The first step was splitting the period into months with method #1 in The Cross Join Collection:
SELECT * FROM [data$] d, [lookup$] l WHERE d.[PeriodStart] <= l.[MonthEnd] and d.[PeriodEnd] >= l.[MonthStart]
Thereafter were following formulas created and added to the view:
DATEDIFF('day', [PeriodStart], [PeriodEnd]) + 1
DATEDIFF('day', MAX([MonthStart], [PeriodStart]), MIN([MonthEnd], [PeriodEnd])) + 1
[Amount] * [Days Month] / [Days Period]
I wish there was a built-in way to split date periods into multiple rows.
If you agree, please consider up-voting Date period dimension type with built-in row split
I am curious to hear if you found a solution. If Re: Divide a value between months was the correct answer, it would be nice for me if it was marked with a green star and probably also for others who are looking for a similar solution.
I am curious to hear if you found a solution. If Re: Divide a value between months was the correct answer,
Sorry Johan, I don't know... I guess we'll never find out.
it would be nice for me if it was marked with a green star and probably also for others who are looking for a similar solution.
but I can deal with this, you're welcome!