Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello --
I have a project to create a production variance dashboard. I'm in the initial stages of trying to gather all of my data.
One of the key aspects to this dashboard is it needs to display a straight table that shows the work orders completed on a day, the qty completed, and break out the material, labor, and overhead costs to then calculate some variances.
They wish to see this table like so:
Part No | Work Order | Qty Completed | Std. Material Cost | Std. Labor Cost | Std. Overhead Cost |
---|---|---|---|---|---|
123456 | XYZ | 10 | .05 | .28 | .32 |
The trouble I am having is that my data exists in 3 tables as follows:
Transaction Table:
Part Number | Work Order | Qty Completed |
---|---|---|
123456 | XYZ | 10 |
Cost Table:
Part Number | Cost_ID | Std. Cost |
---|---|---|
123456 | 1 | .05 |
123456 | 2 | .28 |
123456 | 3 | .32 |
Cost_ID Table:
Cost_ID | Cost_Description |
---|---|
1 | Std. Material Cost |
2 | Std. Labor Cost |
3 | Std. Overhead Cost |
How do I join these various tables together, while converting the "rows" of the cost descriptions into columns in order to display the values the way they wish? Further more, I'll then need to utilize the various costs to do further calculations.
I appreciate your help!
i see.
in fact, even having data model i proposed, u may still use straight table. Simple create expressions for each KPI using set analysis.
Of course you may also create your model like Marco suggested. Of course the question is, what else (except this chart) you plan to do in your application
regards
Darek
May you use pivot instead of straight table?
Hi Dariusz --
Thank you for your reply. I could possibly use a pivot table, however they had requested straight table in their design.
There will be additional calculated columns within the table to calculate the variances from std. to actual reported and they do not want users to be able to manipulate the layout for this.
Thanks!
look here:
let ma know if it helps.
regards
Darek
Hi --
I'm sorry, I forgot to make a note of something. The transaction log table will have multiple transaction records for a part & a day that will need to be added together to get the total transaction qty:
Example:
Transaction Date | Part Number | Work Order | Qty |
---|---|---|---|
4/24/2014 | 123456 | XYZ | 5 |
4/24/2014 | 123456 | XYZ | 5 |
This would need to be summarized as 10.
I get somewhat strange results when I try to pivot it in this fashion - resulting in a lot of collapsed rows.
so, try v1
try to check "Always fully expanded" on Properties/Presentation Tab
I hope, that it helps.
regards
Darek
Hi Darek,
I agree, that the cost type should remain a single field/dimension, but if it is required to generate seperate columns in a straight instead of a pivot table, how about this approach:
CostMap:
Mapping LOAD *
FROM [http://community.qlik.com/thread/115553] (html, codepage is 1252, embedded labels, table is @4);
tabCost:
Generic LOAD
[Part Number],
ApplyMap('CostMap', Cost_ID),
[Std. Cost]
FROM [http://community.qlik.com/thread/115553] (html, codepage is 1252, embedded labels, table is @3);
tabTransaction:
LOAD [Part Number],
[Work Order],
Sum(Qty) as Qty
FROM [http://community.qlik.com/thread/115553] (html, codepage is 1252, embedded labels, table is @5)
Group By [Part Number], [Work Order];
hope this might help
regards
Marco
Hi Dariusz -
You're answers have been very helpful and I'm getting close, but now I am running into the issue which is why I believe they requested a straight table.
When I try to do additional formulas against my costs, I get a column for each formula that shouldn't be there.
For example, I need to subtract a value from only the Std. Labor Cost - however when I do this, I get that calculation for each of the different cost types resulting in additional columns that I don't want. See example image below: