Calculation of average salary between two or more periods

[Gustavo_Eduardo]

Good morning to all. I ask you a question, I don't know if it can be done.

I have a table with annual periods and salaries corresponding to said periods.

I have a table where there are orders. Some of those commissions have durations that cover several of them; Suppose an order goes through the months of January, February and March.

Daily Salary - January = 10
Daily salary - February = 11
Daily salary - March = 12

The issue is that there is a list of work dates and they pay you per day worked.

In January 10 days were worked
In February, 28 days were worked
In March 5 days were worked

Total, of days = 43

I have a list of dates from when the job started to when it finished in an EnumList and now I'm giving an example because I counted them manually but I want this to be done automatically.

I need the app to calculate an average salary value corresponding to the proration of the multiplication of:

[Daily Salary - January] x [Days Worked in January] =10x10=100
[Daily Salary - February] x [Days Worked in February] =11x28=308
[Daily Salary - March] x [Days Worked in March] =12x5=60

Total, to be paid = 100+308+60=468
Average Daily Wage = $468/43 days = $10.88/day

Has something similar happened to anyone? Could you solve it?

I have tried to ask the app to count the days of that list of dates that belongs to month 1 and multiply them by his salary. Then month 2 and multiply them by your salary and so on. But doing this for a long time will be impossible, and the performance of the app would be seriously affected.

Thank you Very Much for your answers!

[WillowMobileSys]

If I understand correctly, your concern is a performance issue in re-calculating the Average Daily Wage over many, many month of Wage entries?

You wouldn't want to re-calculate the whole entire number each time.  I would instead save each Monthly average in a table.  Then when you need an average over several Months, select those and use the AVERAGE() function.  

[Marc_Dillon]

---

@Gustavo_Eduardo wrote:

I have a list of dates from when the job started to when it finished in an EnumList

---

I think it'd be better to have another table, where each record is one day of work at one of those jobs. Then you could easily calculate the wage per day, then just sum those up per job. I imagine these records could be helpful in other ways too, like for storing various other data per each day of work.

As it is now, I see 3 options to make the calculation, and none of them are great.

1. Set up a huge expression with several IFS blocks to accommodate for an arbitrary number of possible months.
2. Set up an action loop construct, to iterate through the list of dates, using another column as an iterator variable, calculating the wage per date and adding it up in another column.
3. Hand the calculation off to an App Script, where iteration is an inbuilt function instead of something to force through with crazy action groups.

[gregdiana1]

Well in a way you have provided the solution already. I would also suggest you create a new table which has column which do precisely what you have done by hand. Ie

Start Date End date  days  Year Month Rate/Month  days*RPM  ………..

If required you can add more details as @Marc_Dillon noted. 

Here Sum(days*RPM)/Sum(days) should do the trick where you may filter the results by year, month etc as you did manually. 

You said you have a list of dates so the columns should already exist etc but without details one may only speculate

Good luck. 

[Gustavo_Eduardo]

boys. First of all thanks for your answers. It is actually an app that seeks to anticipate a probable future.

Calculates the business days between two dates discounting holidays and weekends (the user configures the business week according to their needs). If I know the term to carry out the work, if I know the value that I pay per day worked, I will technically know what I must pay at the end of the work (an estimate). However, the problem does not end there, since in Argentina, where I live, economic inflation makes next month's salary higher than the current one. Therefore, the salary will be paid according to the month in which we are and said salaries are already tabulated for a whole year.

You could work 40 days and but if 5 days fall in one month, 30 days in another month and 5 days in another month, the final budget for the work depends on 3 salaries and not one.

That's what keeps me from sleeping.

One option that I have considered is to calculate the budget with the last month's salary, but that would be inflating the economic value because I only take into account the last month and it would be catastrophic for the final consumer. Taking the first month into account would be catastrophic for the company because inflation would make it lose money when paying.

I am thinking that this calculation is very necessary and that not doing it is creating an app that lies, but the other option I have is to dollarize it. Although salaries are not indexed to dollar value, it would be much more stable than doing it to peso value. However, as @Marc_Dillon demonstrates, it is an extremely complex calculation for me.
I'll try to think of a simpler solution and let you know. Thank you very much for your answers.