6 Replies Latest reply on Feb 17, 2016 3:03 AM by Mark Fraser

# Divide a value between months

Given I have a number attribute, and two dates which comprise an interval, how do I make it so that I can show the number split between each month (depending how many days there are in the month)?

Ex: Number = 55, Date1 = 5-15-2014, Date Date2 = 2-7-2014

The end result would be shown in a bar chart with the values split in 3 months:

May: 55 / 17 (17 being the number of days in may)

June: 55 / 30

July: 55 / 2

• ###### 1. Re: Divide a value between months

Hi Márcio

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?

Cheers

Mark

• ###### 2. Re: Divide a value between months

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
ELSE 0 END
```

the results look like this:

• ###### 3. Re: Divide a value between months

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)

• ###### 4. Re: Divide a value between months

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:

Days Period

```DATEDIFF('day', [PeriodStart], [PeriodEnd]) + 1

```

Days Month

```DATEDIFF('day', MAX([MonthStart], [PeriodStart]), MIN([MonthEnd], [PeriodEnd])) + 1

```

Amount Month

```[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

3 of 3 people found this helpful
• ###### 5. Re: Divide a value between months

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.

• ###### 6. Re: Divide a value between months

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!