Refer to the attached image. This is my table view.
What my goal is, is to show month/week/day capacity vs estimated. This is recorded on an operation resource table, and operation table.
I need to show all capacity for every day even if the resource is not scheduled that day. To achieve this I needed to use a calendar table to get the dates that might not exist in the operation table. I still had an issue though where if I selected a resource group, it would still only show the capacity for days it was scheduled. (IE dates did not exist so no data). I got around this by reloading the table again and joining it with the calendar. This let every resource have every day in the calendar so I can show capacity for every day.
My issue now is that I have no link back into the estimated(Scheduled Hours.)
I am currently using the formula:
sum(if(CalendarDate2=[Schedule Load Date] and [Operation Resource Group]=[Operation Resource Group ],(if(opcomplete=1 or [Job Complete]=1 or ShippedProd=1,0,estsethours+estprodhours))))
to show the correct hours estimated, but this takes forever to calculate (3+ mins which in qlikview time is forever to way for a chart to calculate and this time is in the desktop version). It is pretty fast if I select only one resource, but of course that will not be the only case.
Also I am currently using a macro so that any fields selections from the top left table on Operation Resource Group will auto select from the Operation Resource group that is joined to the calendar.
There is also the issue where ONLY selections made to this field changes this auto select. If a user selects a field from say the Job table like Project or part, the Operation Resource Group linked through tables is reduced, lowering the estimated hours through joins, when the Operation resource group with the calendar is not affected leaving the capacity showing for ALL groups which is incorrect.
Does anyone have any ideas on the best way to do this? I am a bit lost and have tried several different approaches on how to do this over the past several months and I still don't know what to do.