5 Replies Latest reply: Jun 1, 2016 4:40 PM by Andrew Walker

# Distribute values between dates

I just reach an impasse.

I need split data at script level:

Example below

Inline table:(with Sundays Dates)

[ Date

.

.

.

01/05/16

08/05/16

15/05/16

22/05/16

29/55/16

05/06/16

.

.

.

]

Another table:

Start               End           value

02/05/16          31/05/16     29

I need to find a way to return table like this:

Date          Value

08/05/16    6   cause dates 02-07/05

15/05/16     7                         08-14/05

22/05/16      7                        15-21/05

29/05/16     7                         22-28/05

05/06/16     2                              because 29-7-7-7-6=2

The second table has more entities.

The one is just an example

• ###### Re: Distribute values between dates

What is the logic for dividing the Value? Is it one for each day?

• ###### Re: Distribute values between dates

Hi Sunny,

01/05 is Sunday

my first day is 02/05 which is Monday

following Sunday (08/05)  I need value if 6 (Mon-Sat)

next Sunday 15/05 I need a full week  (Sunday 08.05 till Saturday 14.05) -7 days

same for next 2 week

Last week on Sunday the 05.06 I need the value of 2 (for 29.05 and 30.05).

The Dates 02.05 and 31.05 are not being taken into calculations just dates between them.

I hope that makes sense

• ###### Re: Distribute values between dates

Hi Robert,

I've written a script that appears to get what you want. I've added an extra period

Start, End, Value

02/05/2016, 31/05/2016, 29

01/06/2016, 30/06/2016, 29

and have an inline table of Sundays that extend before and after these periods.

A straight table gives this, WeekEnd 05/06/2016 appears twice because dates in week ending 05/06/2016 straddle the two periods.

PeriodID WeekEnd Sum(Cnt)
108/05/20166
115/05/20167
122/05/20167
129/05/20167
105/06/20162
205/06/20164
212/06/20167
219/06/20167
226/06/20167
203/07/20164

Here's the script:

Weeks:

WeekEnd

01/05/2016

08/05/2016

15/05/2016

22/05/2016

29/05/2016

05/06/2016

12/06/2016

19/06/2016

26/06/2016

03/07/2016

10/07/2016

];

1

2

3

4

5

6

7

];

Join(Weeks)

Dates:

WeekEnd,

Resident Weeks;

Drop Table Weeks;

//Up to this point we've just fleshed out a simple calendar of WeekEnd dates and the dates that pertain

Periods:

RowNo() as PeriodID,

*;

Start, End, Value

02/05/2016, 31/05/2016, 29

01/06/2016, 30/06/2016, 29

];

IntervalMatch(Date) LOAD Start, End Resident Periods;

Left Join([Periods-1])

1 as Counter,

*

Resident Periods;

Drop Table Periods;

Left Join(Dates)

Counter,

Date,

PeriodID,

Value

Resident [Periods-1];

Drop Table  [Periods-1];

Result:

*,

If(CumCounter <= Value,1,0) as Cnt;

*,

If(PeriodID = Previous(PeriodID),

RangeSum(Counter, Peek('CumCounter')),1) AS CumCounter

Resident Dates Where Counter =1 Order by Date;

DROP Table Dates;

It looks a bit messy and I'm sure it could be simplified.

Kind regards

Andrew