Announcements
cancel
Showing results for
Did you mean:
Partner - Specialist III

## help with splitting value by date

Hi all,

i need some help with splitting a single value across multiple days.

I have attached an example and here is what i am trying to do..

in the example, if you look at row 38 it has a value of 3500 and a date range of 07/02/2015-31/05/2015.

what i want is to create a new field which has the division of 3500 across the date range  which is 113 days but i also only want it to take working days into consideration and it will also need to take the days per month into consideration, e.g the new field will only be against the dates within the date range.

so the above example would be 3500 / 80 (working days) = 43.75

so i would want the new field populated for the dates of 07/02/2015-31/05/2015 (but only the working days)

with 43.75. if i was then to select the month of Feb i would expect to see a total of 656.25 in the new field.

Can anyone help with this please?

1 Solution

Accepted Solutions
MVP

Try this script:

Data:

RowNo() as RowNo,

Sales/DayDiff as SalesPerDay;

NetWorkDays([Date From], [Date To]) as DayDiff;

[Date To],

Brand,

Sales

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Join(Data)

MonthName(Date) as MonthYear

Where not Match(WeekDay(Date), 'Sun', 'Sat');

Date([Date From] + IterNo() - 1) as Date

Resident Data

While [Date From] + IterNo() - 1 <= [Date To];

Output:

Attaching the qvw for your reference.

Best,

Sunny

4 Replies
Partner - Specialist III
Author

Can anyone help with this please?

Anonymous
Not applicable

you can use your datefields and calculate the dersired value

=Sales/NetWorkDays([Date From],[Date To])

But you Need to use at least a straight table instead of table box

Or is there something different you want to achieve? then explain

MVP

Try this script:

Data:

RowNo() as RowNo,

Sales/DayDiff as SalesPerDay;

NetWorkDays([Date From], [Date To]) as DayDiff;

[Date To],

Brand,

Sales

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1);

Join(Data)

MonthName(Date) as MonthYear

Where not Match(WeekDay(Date), 'Sun', 'Sat');

Date([Date From] + IterNo() - 1) as Date

Resident Data

While [Date From] + IterNo() - 1 <= [Date To];

Output:

Attaching the qvw for your reference.

Best,

Sunny

MVP

Hi,

one solution could be:

Data:

Sales/NetWorkDays([Date From],[Date To]) as SalesPerWorkDay,

RowNo() as RowNo

tabCalendar:

Day(Date) as Day,

WeekDay(Date) as WeekDay,

Dual(If(NetWorkDays(Date,Date),'Yes','No'),NetWorkDays(Date,Date)) as IsWorkDay,

Week(Date) as Week,

WeekName(Date) as WeekName,

Month(Date) as Month,

MonthName(Date) as MonthName,

Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

QuarterName(Date) as QuarterName,

Year(Date) as Year,

WeekYear(Date) as WeekYear;

While MinDate+IterNo()-1 <= MaxDate;

Max([Date To]) as MaxDate

Resident Data;

IntervalMatch (Date)