Monthly View / Report on Timesheet App

Sinethemba_Gayi · 05-01-2019 09:12 AM

Hi, How can I achieve setting Monthly Views or Reports on the Timesheet App?
Only the App Admin plus that particular Employee should be able to view the Monthly View or Report?

Aleksi

What kind of monthly view or report are you thinking? Do you have more details?

Sinethemba_Gayi

Let’s assume there’s 5 employees.

At the end of the month or week, the current App displays all employees in descending order.

I want to be able to view Employee 1 Timesheets for a particular month and calculate Total Hours Worked + Total Overtime Hours Worked + Total Pay for that month or week

Aleksi

I assume you are using gSheet as a backend and you want to see the summary from the last month. Or do you want to see every month one by one from the past?

Sinethemba_Gayi

Yes I am using gSheet
I would like to be abme to see every month one by one, per employee as well.

This will make it easy for payroll purposes.

Aleksi

I would probably do this with an extra table with one column (employee emails). When you have that table, you can calculate your numbers with virtual columns.

Sinethemba_Gayi

Thanks @Aleksi, can I please burden you with a request of the said formula and instructions.

My Timesheet has:
Date,
Time In and Time Out (DateTime format)
Total Hours Worked
Normal Hours,
Overtime Hours,
Normal Hourly Rate,
Overtime Rate,
Normal Pay,
Overtime Pay
Total Pay

Sinethemba_Gayi

Hi @Aleksi , any tips as to how I can achieve “I would probably do this with an extra table with one column (employee emails). When you have that table, you can calculate your numbers with virtual columns”?

I would really appreciate it!

Aleksi

Create one extra table (if you don’t have users table already) with one physical column. This column contains all your users like email adddress or name. It doesn’t matter what the value is, it needs to be unique and the same value you will need to have in your Timesheet table. I’m assuming that it’s a name field.

Now you can sum last months total pays with the virtual column like…
SUM(
SELECT(Timesheet[Total Pay],
AND(
YEAR(EOMONTH(TODAY(),-1))=YEAR([Date]),
MONTH(EOMONTH(TODAY(),-1))=MONTH([Date]),
[UserName]=[_THISROW].[UserName]
)))

Better approach would be if you have that users table and you use Ref column in your Timesheet table when you choose the user. Then the formula would not need to read the whole Timesheet table, just that related virtual list. Then the syntax could be like…
SUM(
SELECT([RelatedTimesheetRecords][Total Pay],
AND(
YEAR(EOMONTH(TODAY(),-1))=YEAR([Date]),
MONTH(EOMONTH(TODAY(),-1))=MONTH([Date])
)))

Sinethemba_Gayi

Thanks @Aleksi,

I changed TotalPay to TotalWages and UserName to Email as per my columns. However, I get a R0.00 sum.
I set the Type to “Price”

Brent_Meade

This suggestion returns a value of $0.00. I do not understand the next comment about using ANDs one by one. Anyone got this to work?

Aleksi

Try to add those ANDs one by one so you could see how it reads your data.

John_Krenkel

Would I be able to do something similar to calculate time across a week span. I am trying to distinguish between regular time and overtime hours. so i need to be able to look across an employee and date span and calculate their regular time. Was thinking of having a cumulative time column which calculates all the time just for the week. then splitting it in a regular and overtime using a max formula.

Any direction would be helpful

Aleksi

It sounds that you would need to add an additional column for the overtime hours.