After script linking and data flow issue

[#1]

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.

[#2]

Alright I was able to resolve the issue with selection with a macro that will always select possible values in the field on any change of the original field.

I still however have the issue of chart calculation time. Using an if statement takes far far to long to calculate for all data.

[#3]

All weekend without a response. Should I give up on what I am trying to do?

[jonathandienst]

Hi

I think you may have confused the issue (at least for me ) by referring to the load time. This normally means the time t takes after you hit the reload button, but I think you meant the time it takes to calculate the object on the front end.

Sum(If()) is usually slow. For small models, this does not matter, but as it gets bigger, and the model gets more tables, it can do what you are seeing.

Two suggestions:

• Use set analysis rather than sum(if()).

               Sum({<CalendarDate = P(ScheduledLoadDate), .....

          This won't work if the values in the If statement are dimensions, as the set expression is evaluated before the chart is built.

• Optimise the model structure. Try to ensure that all the fields in the sum(if()) are in the same table. You might be able to get away with one or two tables apart if the model is small, but the bigger the tables, the less 'hops' to evaluate the expression.

Hope that helps

Jonathan