Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Plant Operation Efficiency Measures

Hi, everybody!

I need to find a way to measure the efficiency of different groups in our plant based on sales and the number of hours worked. Some employees are dedicated to a product line while others belong to groups that support all product lines.

I have two different data sets, one has sales by product line by month and the other has hours worked by group.

Prod LnDateSales
Product 16/1/2015200000
Product 26/1/2015250000
Product 17/1/2015225000
Product 27/1/2015180000
Product 18/1/2015230000
Product 28/1/2015150000

GroupMonthHours Worked
Product 1 Employees06/01/2015688
Product 1 Employees07/01/2015720
Product 1 Employees08/01/2015735
Product 2 Employees06/01/2015860
Product 2 Employees07/01/2015900
Product 2 Employees08/01/2015880
Support Group A06/01/2015344
Support Group A07/01/2015350
Support Group A08/01/2015320
Support Group B06/01/2015516
Support Group B07/01/2015530
Support Group B08/01/2015550
Support Group C06/01/2015172
Support Group C07/01/2015172
Support Group C08/01/2015172

The results that I'm looking to get would be like this where Product 1 Employees are measured against only Product Line 1 sales, but Support Group A is measured against the sum of Product 1 and Product Line 2 sales.

  

Results06/01/201507/01/201508/01/2015
Product 1 Employees $    290.70 $    312.50 $    312.93
Product 2 Employees $    290.70 $    200.00 $    170.45
Support Group A $ 1,308.14 $ 1,157.14 $ 1,187.50
Support Group B $    872.09 $    764.15 $    690.91
Support Group C $ 2,616.28 $ 2,354.65 $ 2,209.30

Does anyone know the the best way to build this in QlikView? Ideally the end user will click on just the Group they way to see the efficiency for and the expression will know whether to add just the product line sales or total sales.

The tables are attached in an Excel file.

Thanks for the help!

Justin

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution in the front end keeping your data in seperate tables could be:

QlikCommunity_Thread_167180_Pic5.JPG

QlikCommunity_Thread_167180_Pic4.JPG

QlikCommunity_Thread_167180_Pic1.JPG

QlikCommunity_Thread_167180_Pic2.JPG

QlikCommunity_Thread_167180_Pic3.JPG

table1:

LOAD [Prod Ln],

     MonthName(Date) as Month,

     Sales

FROM [https://community.qlik.com/thread/167180] (html, codepage is 1252, embedded labels, table is @1);

table2:

LOAD Group,

     MonthName(Month) as Month,

     [Hours Worked]

FROM [https://community.qlik.com/thread/167180] (html, codepage is 1252, embedded labels, table is @2);    

hope this helps

regards

Marco

View solution in original post

5 Replies
sunny_talwar

Is this what you are looking for?

Capture.PNG

If yes, you can use the following script:

Table:

LOAD Capitalize(Group) as [Product Line],

    Month,

    [Hours Worked]

FROM

Community_167180.xlsx

(ooxml, embedded labels, table is Sheet3);

Join(Table)

LOAD Capitalize([Product Line]) as [Product Line],

    Month,

    Sales

FROM

Community_167180.xlsx

(ooxml, embedded labels, table is Sheet2);

Join(Table)

LOAD Month,

    Sum(Sales) as Sales1

FROM

Community_167180.xlsx

(ooxml, embedded labels, table is Sheet2)

Group By Month;



and then create a pivot table with Product line and Month as dimension and the following expression:

=Sum(Alt(Sales, Sales1))/Sum([Hours Worked])

Please find attached the qvw file for reference also.

Best,

Sunny

MarcoWedel

Hi,

one solution in the front end keeping your data in seperate tables could be:

QlikCommunity_Thread_167180_Pic5.JPG

QlikCommunity_Thread_167180_Pic4.JPG

QlikCommunity_Thread_167180_Pic1.JPG

QlikCommunity_Thread_167180_Pic2.JPG

QlikCommunity_Thread_167180_Pic3.JPG

table1:

LOAD [Prod Ln],

     MonthName(Date) as Month,

     Sales

FROM [https://community.qlik.com/thread/167180] (html, codepage is 1252, embedded labels, table is @1);

table2:

LOAD Group,

     MonthName(Month) as Month,

     [Hours Worked]

FROM [https://community.qlik.com/thread/167180] (html, codepage is 1252, embedded labels, table is @2);    

hope this helps

regards

Marco

Not applicable
Author

Thanks!

Not applicable
Author

Thanks, Marco!

MarcoWedel

You're welcome

regards

Marco