Trying to create a visual weekly graph

[bjames]

On my staff page in the app I have the following visual (obviously some details are blacked out).  

The 'Tracked Hours This Week' comes from a virtual column in the staff table with the following formula:

SUM(
SELECT(
Timesheets[Total Time (Hrs)],
AND(
[Staff ID] = [_THISROW].[Staff ID],

IF(WEEKDAY(TODAY())=1,
AND([Date]>=TODAY()-6 ,[Date]<=TODAY()),
AND([Date]>=EOWEEK(TODAY())-5 ,[Date]<=EOWEEK(TODAY())+1)

)

)
)
)

As a number it works perfectly.  But I would like to display this as a graph where it shows how many hours have been tracked on each day of the week.  I have no idea how to create this, does anyone have any suggestions?

Thanks in advance.

 

[Suvrutt_Gurjar · Accepted solution]

Okay, got it. Please try the following.

Please add another VC called say [Total Time] in the Timesheets table with an expression something like

TOTALHOURS([Total Time(Hrs)])

Please ensure this new column is available in the table/slice of the chart. 

Please see if you get SUM: Total Time option in the Group Aggregate setting.

You could base the chart aggregate on this VC then.

[Suvrutt_Gurjar]

Could you update how are you displaying /computing the "Tracked Hours Today" column in the view you have shared?

And more importantly, is there any referencing relationship between the "Staff" and "Timesheets" tables?

[bjames]

@Suvrutt_Gurjar 

Heres the expression for Tracked Hours Today 

SUM(
SELECT(
Timesheets[Total Time (Hrs)],
AND(
[Staff ID] = [_THISROW].[Staff ID],
[Date] = TODAY()
)
)
)

Here is the staff table

and Here is the Timesheet table 

So references via staff table and Staff ID.

Is that what you needed?

[Suvrutt_Gurjar]

Thank you. Yes, this is what I meant.

Please try below.

1. Create a virtual column called say 'Timesheets_Current_Week" in the "Staff"  table with an expression something like 

SELECT([Related Timesheets][Timesheet ID], 

IF(WEEKDAY(TODAY())=1,
AND([Date]>=TODAY()-6 ,[Date]<=TODAY()),
AND([Date]>=EOWEEK(TODAY())-5 ,[Date]<=EOWEEK(TODAY())+1)

) )

Please ensure this column is available in the detail view of the Staff table whose screenshot you shared.  Column Type: List,  Element Type: Ref,  Referenced Table Name: Timesheets

2. Please create a chart view with with name say "1- Timesheet charts". on the 'Timesheets" table.  Please ensure you prefix the name with "1-"

Chart Type: Histogram ,  Group Aggregate:  SUM: Total Time(Hrs), Chart Column: [Date], View Position: Ref

I believe then the timesheet chart with daily hours by weekly dates should start reflecting in the "Staff" table's detail view as an inline chart view.

[bjames]

@Suvrutt_Gurjar 

I'm having a problem with this section - Chart Type: Histogram ,  Group Aggregate:  SUM: Total Time(Hrs), Chart Column: [Date], View Position: Ref

here is the screenshot

Can you see what I have done wrong?

[Suvrutt_Gurjar]

Are you not getting the option of "SUM: Total Time(Hrs) under the Group Aggregate setting?

[bjames]

No, I just have two options, NONE and COUNT

[Suvrutt_Gurjar · Accepted solution]

Okay, got it. Please try the following.

Please add another VC called say [Total Time] in the Timesheets table with an expression something like

TOTALHOURS([Total Time(Hrs)])

Please ensure this new column is available in the table/slice of the chart. 

Please see if you get SUM: Total Time option in the Group Aggregate setting.

You could base the chart aggregate on this VC then.

[bjames]

Ok, so I created a VC in the 'Timesheet (1)' table called Total time.  Then when back to the new view and I do now have  SUM: Total Time option in the Group Aggregate setting.  So I'm guessing it works.  But in the preview window its showing a chart that has all the times entered for the whole year, by all staff.  What I was hoping for was a chart for the logged in user for the current week only.  Does this make sense?  Heres the screenshot:

[bjames]

Oh I'm sorry, when I click on and individual staff member, it show just the time for that staff member and the hours for that week, so I think thats working perfectly.  Thank you so much.

[bjames]

@Suvrutt_Gurjar 

Is there a way to have the day of the week at the bottom, instead of the date: 

[Suvrutt_Gurjar · Accepted solution]

Good to know it works. Yes, the chart will show only the concerned staff's hours in the related view. If you see the chart view in isolation it will display all records. Anyway the user will never see the chart view in isolation because it is a ref view.

Regarding weekday, please create yet another VC called say [Day_of_Week] with an expression something like 

SWITCH(WEEKDAY([Date]),

                 1, "1-Sunday",

                 2, "2-Monday",

                3,  "3-Tuesday",

                4,   "4-Wednesday",

               5, "5-Thursday",

                6,   "6-Friday",

               7,  "7-Saturday",

                "Holiday"

                )

In the chart view, please use this column instead of the [date] column.

Edit: Sorry forgot that a much compact version for getting the weekday would be

CONCATENATE( WEEKDAY([Date]) ,"-",TEXT([Date], "DDDD"))