All good so far. the Hours Worked Amount is a simple maths calculation column.
What I need to do is now create a sub total column of the hours worked amount which only calculates the specific values in this column for the specific week ending column.
For examples: July 15th has 2 days that have been worked. Hence I need to calculate $677.00 + $155.43 which then equals the total amount for this specific week ending data.
Then I will need to set up a column that then calculates for the Month of July the 4 specific week ending dates for the month, based on the sub total.
Please note: I cannot do a simple roll up as this will not give the view of the week ending sub total or the monthly total.
I thought maybe a if - then - else however this does not appear to work either but did come the closest to what I am after, however not exact.
I thought it might be a sub total query column however couldn’t work this one out.
I thought of a look up column but because the sub total column is not calculated cannot do the job so to speak.
I thought of setting the Hours Worked Sub Total as a single value column however this doesn’t appear to work either.
Any and all suggestions would be greatly appreciated. Please give step by step process in your response so I can follow as I still have my Glide training wheels on.
Many thanks in advance and all support and assistance is greatly appreciated.
It should be: Week Ending is within This Row → Week Ending
Essentially your query should match all the rows that have the same Week Ending as the current row.
This is sometimes referred to as a self-relation.
The “This Row” is easily missed in the Filter. You have to look carefully for it.
Gidday Darren,
Many, many thanks for your response - very greatly appreciated.
Yes, I missed the “this row” and very helpful image, however it appears I still cannot get this to total and I’m blowed if I know exactly what I’m doing incorrectly.
I also get the yellow warning label, however, I still cannot seem to obtain the relevant week ending total - still shows for each individual week - which is useful however to have the addition of the days worked within the same week ending (if more than 1 day) appears to escape me.
The info simply doesn’t appear in the data sheet with the query in place, nor does it appear in the drop down menu for the fields.
It’s probably still something simple that I’m missing however it still seems to escape me.
Penny for your thoughts please.
Many thanks in advance - For me the excel spreadsheet had the formulas worked out but these formulas do not transfer into Glide when the spreadsheet is imported.
Very greatly appreciate all the help and support I am receiving.
Once you have the Query column correct, you then create a Rollup column.
The Rollup column should target the column that you want to sum via the Query colum.
And the output of the rollup column will be the result you are looking for (if you do it correctly).
However I see am unable to see the actual sub total amount in the fields area. Is this because it is a query column please for specific rows.
When I do the roll up I receive the running total for all the rows in the column value (currently there are 26 rows. Now whilst this running total will be useful for my friend.
Do I then need to take this running total and say / by 26 fortnights or 12 weeks.
Will this be the way to go please.
Once again, many, many thanks for all the support and assistance you are providing - very greatly appreciated.
With the relation column it gives the complete roll up for the entire column thereby giving a running total. rather than the total i.e. for the specific month
