Query Function, Timesheet Data Viz

Question

I'm working on a timesheet dashboard for my office. My goal is to create summaries of weekly, monthly and yearly time spent on overhead vs. projects. I have based the formula on Player0's work that organize the data into weekly, monthly, and yearly results (all hours added up). I do not know how to separate overhead work from time on project work. Sample Sheet:

https://docs.google.com/spreadsheets/d/1IRtS60P256_fo-Yen1IKV4U6SkNpZJWc1Cf8Jeqw_20/edit?usp=sharing

Desired Output: Please refer to Sample Sheet, Dashboard tab, for example.

Generating TOTAL HRS should be simple in a Query function, by adding up OVHD HRS + PROJ HRS columns (once created).

Generating % PROJ HRS should be simple in a Query function with basic athematic.

What would we use to generate OVHD HRS and PROJECT HRS as new columns?

Thank you in advance for your insights.

Player0's post: Sort timesheet logged hours by week

I've tried using SUMIF function to create the additional columns within Query, but get an error (number of rows doesn't match...).

you can use QUERY to do all those things..., let's say you have a DATA array which col1 is OVHD HRS, col2 is PROJ HRS, you can do ```=QUERY(DATA,"SELECT Col1,Col2,Col1+Col2,(Col2/(Col1+Col2))*100 LABEL Col1'OVHD HRS',Col2'PROJ HRS',Col1+Col2'TOTAL HRS',(Col2/(Col1+Col2))*100'% PROJ HRS'")``` — Ping, Nov 29 '22 at 06:23
if your question is "how to get OVHD HRS" from the source, that would be different, since the ```PROJECT #``` value in source sheet is always ```0``` for OVHD HRS, you can use that as an indicator: ```=QUERY({'D_Transpose'!A2:E;'D_Transpose'!G2:H;'D_Transpose'!M2:Q},"WHERE Col2=0",0)``` — Ping, Nov 29 '22 at 06:30
or, you can also lookfor the string ```(OVHD)``` in column 3: ```=QUERY({'D_Transpose'!A2:E;'D_Transpose'!G2:H;'D_Transpose'!M2:Q},"WHERE Col3 CONTAINS '(OVHD)'",0)``` — Ping, Nov 29 '22 at 06:33
Ping, this is absolutely amazing! Thank you so much for the time it took to answer my question -- it clearly was much more complex than I thought. It will take me time to understand your work. — Brad Prestbo, Nov 29 '22 at 18:35
This example only included three staff members (FF, JB, TG). How would I add additional staff? I see the FFRAW, JBRAW and TGRAW, but do not understand how these are used. Thank you. — Brad Prestbo, Nov 29 '22 at 18:36
If you can understand how ```LAMBDA()``` and ```QUERY()``` works, you will be able to understand this formula. documents can be found: LAMBDA: (https://support.google.com/docs/answer/12508718?hl=en), QUERY: (https://developers.google.com/chart/interactive/docs/querylanguage) — Ping, Nov 29 '22 at 19:05
to make it short, you can see that there are 2 lines which have the FFRAW, JBRAW, etc. thing, these are variables, ```LAMBDA()``` is used mainly to declare variables so you don't have to repeat the same code again and again in other functions. — Ping, Nov 29 '22 at 19:08
every ```LAMBDA()``` must be followed with another ```()```, it wroks like this: ```LAMBDA(Variable_1,Variable_2,Calculation)(Value of Variable_1, Value of Variable_2)```, you can basically use any plain text as variable, and any thing as the value (including Function, Array, Number, Boolean and String. The last entry inside the ```LAMBDA()``` will always be executor, where you can use the declared variables to do whatever you need to. — Ping, Nov 29 '22 at 19:14
```LAMBDA()``` accept from 1 to many variables. (there is a limit I think but I never exceeded), but it need at least 1 variable to work. — Ping, Nov 29 '22 at 19:17
to add new staff members, you will need to add them as variable, add the related query as value, and also stack the new entry into the value of the ```DATARAW``` variable. — Ping, Nov 29 '22 at 19:19
@Ping, thank you for your **generosity** with this solution, and the explanations. I have some studying to do :) — Brad Prestbo, Dec 01 '22 at 23:48

Ping · Answer 1 · 2022-11-29T10:43:23.623

The one formula which do everything is here.

put the formula into C2 as shown in the images.
in A3, select in which method do you want to display the results.
in A4, select in which staff do you want to display the results.

This formula mainly uses QUERY() to modify data from the given range, togather with some other date functions.

=ArrayFormula(LAMBDA(SHOW,STAFF,
 LAMBDA(RANGES,
  LAMBDA(HEADERS,JBRAW,FFRAW,TGRAW,
   LAMBDA(DATARAW,
    LAMBDA(YEARS,MONTHS,MONTHLY,WEEKS,WEEKNUMS,TYPES,
     LAMBDA(DATA,
      LAMBDA(SHOWWEEK,ORDER,
       LAMBDA(RESULT,
QUERY(IF(RESULT="",0,RESULT),"SELECT Col1,Col2,Col3,Col2+Col3,(Col3/(Col2+Col3)) LABEL Col2+Col3'TOTAL HRS',(Col3/(Col2+Col3))'% PROJ HRS' FORMAT (Col3/(Col2+Col3))'#,##0.00 %'",1)
       )(QUERY(QUERY(QUERY(DATA,"SELECT "&SHOW&",SUM(Col6) "&SHOWWEEK&" WHERE Col2='"&STAFF&"' GROUP BY "&SHOW&SHOWWEEK&" PIVOT Col1",1),"ORDER BY "&ORDER,1),"SELECT Col1,Col2,Col3",1))
      )(IF(SHOW="Col7",",Col5",""),IFS(SHOW="Col7","Col4 DESC",SHOW="Col8","Col1 ASC",TRUE,"Col1 DESC"))
     )(QUERY({DATARAW,YEARS,MONTHS,WEEKS,WEEKNUMS,TYPES,MONTHLY},"SELECT Col10,Col1,Col6,Col7,Col8,Col5,Col9,Col11 LABEL Col6'YEAR',Col7'MONTHS',Col8'WEEKS',Col9'WEEK',Col11'MONTH'",1))
    )(
     YEAR(INDEX(DATARAW,,4)),
     MONTH(INDEX(DATARAW,,4)),
     TEXT(INDEX(DATARAW,,4),"mmmm"),
     WEEKNUM(INDEX(DATARAW,,4)),
     IFERROR("WEEK "&WEEKNUM(INDEX(DATARAW,,4))&" "&YEAR(INDEX(DATARAW,,4)),""),
     IFS(INDEX(DATARAW,,2)="PROJECT #","TYPE",INDEX(DATARAW,,2)=0,"OVHD",INDEX(DATARAW,,2)>0,"PORJ")
    )
   )({HEADERS;JBRAW;FFRAW;TGRAW})
  )(
   ARRAY_CONSTRAIN(RANGES,1,5),
   QUERY(RANGES,"SELECT "&JOIN(",","Col"&SEQUENCE(5,1,1))&" WHERE Col1 IS NOT NULL LABEL "&JOIN(",","Col"&SEQUENCE(5,1,1)&"''")),
   QUERY(RANGES,"SELECT "&JOIN(",","Col"&SEQUENCE(5,1,7))&" WHERE Col7 IS NOT NULL LABEL "&JOIN(",","Col"&SEQUENCE(5,1,7)&"''")),
   QUERY(RANGES,"SELECT "&JOIN(",","Col"&SEQUENCE(5,1,13))&" WHERE Col13 IS NOT NULL LABEL "&JOIN(",","Col"&SEQUENCE(5,1,13)&"''"))
  )
 )(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1IRtS60P256_fo-Yen1IKV4U6SkNpZJWc1Cf8Jeqw_20/edit#gid=1822160043","'D_Transpose'!A:Q"))
)(IFS($A$3="Weekly","Col7",$A$3="Monthly","Col8",$A$3="Yearly","Col3"),$A$4))

Query Function, Timesheet Data Viz

1 Answers1