Qlik Healthcare User Group

New Contributor III

Obtaining single table value for Chart Formula?

Hi

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

Rick

Staffed=if(ChtHr=Sch24Hr and Sch24Day=WeekDay(Date_Ver), Sch24Staff, 100) * 7.8  // Sch24Staff filed not being recognized

Best

Rick

Message was edited by: Richard Ouellette to add ExampleSchedule2.qvw 2/11/2017.

Tags (1)
4 Replies
MVP

Re: Obtaining single table value for Chart Formula?

Non of your tables are connected to each other. What exactly are you looking to do here? What is the expected output?

MVP

Re: Obtaining single table value for Chart Formula?

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:

DImension: Sch24Hr

Expression: Sum(Sch24Staff)

-Rob

http://qlikviewcookbook.com

Highlighted
New Contributor III

Re: Obtaining single table value for Chart Formula?

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.

Not applicable

Re: Obtaining single table value for Chart Formula?

Hi,

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.