I have a table called Staff24 consisting of 168 values associated with 7 X 24 days/hours that I would like to apply to a daily 24 hour activity chart. Since a single value is required from the Staff24 table for each hour of the day I am having problems with the formula below. Attached is a mock up of my effort. Can you take a look and advise? Thanks
Staffed=if(ChtHr=Sch24Hr and Sch24Day=WeekDay(Date_Ver), Sch24Staff, 100) * 7.8 // Sch24Staff filed not being recognized
Message was edited by: Richard Ouellette to add ExampleSchedule2.qvw 2/11/2017.
I think you may be misunderstanding how to approach QV (or I am misunderstanding the question). You are using two disconnected island tables in your model, an advanced and usually unnecessary technique.
What are you trying to show? If you are trying to show staff count per hour then:
I would like to tie the Staff24 table to TestDetail and got hung up with this formula. I suspect that an apply map approach will be needed.
Are you trying to put on the chart both: scheduled headcount and actual headcount?
I think you can link those tables with a composite key like : WEEKDAY-HOUR, and later add both:
count(ID) and sum(Sch24Staff) as measures - this is solution for browsing single day at time.
You can also map Sch24Staff to the other table in the load script. I think it will be most efficient. Also for performance.