Filter and sum a Gallery, based on the items position (x property) Gantt chart format

(0) Share

Report

Posted on by BrendanZ1

333

Hi all,

This might be a tricky one to explain, but here goes:
Based on the following video, I have setup a gallery to work like a Gantt chart that covers 24hrs as opposed to a year.

https://www.youtube.com/watch?v=IPmFSCM4vH0

I use the field "Starting" where the user puts in a value between 0 and 23 which then determines where the label of the item is positioned using the 'X' property. Formula is: ThisItem.Starting * galGantt.Width / 24

The width of the label is based on a field called EstHours. So if the user puts in the number 3 for Starting (3am), then EstHours as say 6, then the task will finish at 9am.

Each item (stored in SP list) has a field called StaffRequired which is a number field. So a task might have 2 staff required and tasks will overlap. What I was hoping to do, is at the bottom of the gallery, calculate how many staff are required each hour, based on the tasks logged. I can get value that sums the StaffRequired column, but I can't figure out how to separate the item based on an hourly value (if that makes sense). See the screenshot for hopefully better information. gantt powerapps.png

So at the bottom it determines which items sit in the 3am-4am slot and adds up the staff required for those items. Then another label for the 4am-5am slot and so on.

Let me know if more info is needed and I'll do my best to post it here.

Thanks heaps!

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

v-bofeng-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @BrendanZ1 :

Do you want to get something like this?

I've made a test for your reference:

1\I assume there is an event table:

ClearCollect( TheEvents, {EventName:"Event1",Start:"00:00",End:"04:00",Person:40}, {EventName:"Event2",Start:"03:00",End:"21:00",Person:100}, {EventName:"Event3",Start:"02:00",End:"22:00",Person:15} )

2\Add a blank horizontal gallery(Gallery1)

Items

[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23]

TemplatePadding

0

TemplateSize

50

X

0

TemplateFill

If(Mod(ThisItem.Value,2)=0,RGBA(0,0,0,.1))

3\Add a label into Gallery

Width

50

Text

Text(ThisItem.Value,"[$-en]00")&":00"

4\Add an other label to show numbers

Text

Sum( Filter( TheEvents, Value(Left(Start,2))<=ThisItem.Value && Value(Left(End,2))>=ThisItem.Value ), Person )

5\Add an other blank vertical gallery(Gallery2) cover Gallery1

Items

TheEvents

6\Add an Rectangle into Gallery2

X

50*Left(ThisItem.Start,2)

Width

50*(Left(ThisItem.End,2)-Left(ThisItem.Start,2)+1)

Best Regards,

Bof
BrendanZ1 333 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @v-bofeng-msft for the help once again. I will be able to use some of this method I hope, but have changed my scope somewhat with the gantt chart. What I am trying to do is display the tasks based on the start and finish times from two date and time fields (JobStart, and JobComplete).
So far I can get the width to work and display based on an hour count from the JobStart date/time. What I am struggling with, is getting the 'X' on the task label to work.
The chart is setup with 24 sections as per before at 65px widths (Gallery is 1560 wide). I need to convert the JobStart date/time into a value that I can then use to determine the 'X' position of the label.

As an example, one of my tasks has a start date/time of 16/03/2021 7:00AM. In order to get it to show on the chart/gallery in the right spot, it's 'X' position should be 455, which is the start of the 7am slot. (7 x 65).

I am at a loss as to how to go about converting a time into a number that can be referenced.......
BrendanZ1 333 on at

Like
a
(0)

Report
Copy link

Link copied!

There are a few other functions that would go with this to enable a complete gantt chart that displays the task label in an accurate way.
When filtering the gallery by the date, in the instance a task goes into the following day, the task label will automatically start with an 'X' position of 0 in the gallery (representing midnight).
So I need to identify that 0 is equal to midnight, then for the label to work out the difference between the date and time at this point (midnight or 0:00), and the expected end date and time. So for example, the task is started at 4pm today and is scheduled to finish at 5am tomorrow. It would need to determine that this is equal to 5 hours and therefore the width will match this value.
Hopefully that makes sense