### Post

2 followers Follow # Calculating index and projections from days gone by

Hi ladies end gent's, here's a challenge?

1. In "Reporting" I have 25 number-fields each representing a day where Sales Managers can input sales numbers.

2. I also have another app called "Date" where I input total sales days of the month and each morning I input days gone by, and a calculation is made of days left.
3. Last I have an app "Target" where I input salestarget once a month.

In "Reporting" I sum up the numbers of all 25 fields in a calculation field and then its' possible to calculate:

INDEX:
@Sum of sales / @Sum of days passed *@Sum of days left /@Sum of sales-target *100
PROJECTIONS:
@Sum of sales /@Sum of days passed *@Sum of Total days

QUESTION: Is there a workaround so I can skip the part with changing days gone by, each day?

Instead of calculating dates, I think I would prefer to count how many of the 25 number fields that have been filled out (with 0 as well) so the correct calculations is made in each item because I would get the wrong result if some of the Sales Managers have filled out sales numbers but others have not, and they all refer to the same "Date" app.

Alternatively I could count days between two date fields inside the reporting app, but then I would have to change date end each day. Can this code below from Rainar be tweaked to @currentday instead of @enddate? Is there even a javascript code for current -And would that be a solution?

__________________________________________________

var startDate = @DateStart
var endDate = @DateEnd

function workingDaysBetweenDates(startDate, endDate) {

// Validate input
if (endDate < startDate)
return 0;

// Calculate days between dates
var millisecondsPerDay = 86400 * 1000; // Day in milliseconds
startDate.setHours(0,0,0,1); // Start just after midnight
endDate.setHours(23,59,59,999); // End just before midnight
var diff = endDate - startDate; // Milliseconds between datetime objects
var days = Math.ceil(diff / millisecondsPerDay);

// Subtract two weekend days for every week in between
var weeks = Math.floor(days / 7);
var days = days - (weeks * 2);

// Handle special cases
var startDay = startDate.getDay();
var endDay = endDate.getDay();

// Remove weekend not previously removed.
if (startDay - endDay > 1)
days = days - 2;

// Remove start day if span starts on Sunday but ends before Saturday
if (startDay == 0 && endDay != 6)
days = days - 1

// Remove end day if span ends on Saturday but starts after Sunday
if (endDay == 6 && startDay != 0)
days = days - 1

return days;
}
startDate == null || endDate == null ? "" : workingDaysBetweenDates( new Date(startDate) , new Date(endDate))

__________________________________________________

Daniel Barnes

### 1 comment Hi again

as far as I know, it's very hard to automatically update a date. Rainar, that very, very generous and helpful man has answered hundreds of questions in regards to this subject.
Also, Globiflow is required sometimes, but it seems that I have just figures ot a very cool solution.

Using Zapier, I can update a date field every day.

So I have two date fields. The first one is the start date and the second one is the one that should be updated daily for Rainars formula to work automatically.
I start building the Zap with a trigger at 1 am and connect to Podio and have Zapier change the date to current day.
In Rainars fantastic formula above I get a result that is one day more than I need, because the current day has not yet finished -saleswise. I just subtract that day in my formula to calcutale index and Voila. I need not to do anything the rest of the month. My sales Managers can solely update sales numbers and nothing else.

New Index calculation:

@Sum of sales  / (@Sum of days passed -1) *@Sum of Total days /@Sum of Point-target *100

"Sum of days passed" and "Sum of total days" are references from my "Date" app

I really hope that this makes sence. Take into account that I could'nt code if my life depended on it, so this is workaround on workaround to accomplish what I want.

Daniel Barnes 0 votes