8 Replies Latest reply: Jun 25, 2014 1:50 PM by Joe Hutchings RSS

    Trouble charting data

    Joe Hutchings

      Hello --


      I am a beginner to Qlik and I'm working on my first big dashboard project. I am creating a Productivity Variance dashboard for our finance /production teams.


      They have asked for a series of graphs but also want the straight table data underneath to allow them to analyze the raw data they have drilled down on.


      The specific problem I am having is that they would like to be able to chart the overall variance hours (std production hours vs. actual production hours) by Qtr, Month, Week, Customer, Part #, and any combination of these.


      How would I go about doing this? I started out by creating individual charts and then grouping them in containers - but I get the problem that some of the charts say "No data to display"  until I've drilled down to a certain level (say to a specific customer & part number).


      My expression to gather this metric is fairly complex, and I'm wondering if that is my problem? In order to gather what I need, I have to do math against actual transacted qtys against the costs for the part, and then against the actual process hours reported for that workorder.




      I appreciate any and all help - and will gladly provide any more detail that is needed.



        • Re: Trouble charting data
          christian juillard

          Hi joe


          would it be possible to have a set of data and an example of graphs you need ?


          best regards


            • Re: Re: Trouble charting data
              Joe Hutchings

              Attached is the sample data.


              I am loading 4 primary tables:


              • Transactions - Has all the transactional data for work orders
              • Costs - Has all the labor costs for parts
              • Labor Hours - Has the labor hours reported for a work order
              • Customer - Has the customer name linked to the part number


              Transactions.Part_ID -> Costs.Part_ID

              Transactions.Workorder_ID -> Labor Hours.WorkOrder_ID

              Transactions.Part_ID -> Customer.Part_ID


              In order to calculate my standard labor minutes for a part, I have to perform math against the cost. We're only trying to capture the productivity at the top level, so if the part has a sub-assembly I need to use the labor cost less the subassembly. If the part does not have a sub assembly I use just its labor cost.


              The formula I am using looks like this for that:




              This formula should give me the calculated labor std minute for a part based on its labor cost (the 9.46 is a standard labor dollar amount that I made up for this sample)


              Then in order to calculate the actual labor hours spend on a part, I need to summarize the transactions and labor hours for the work order/part and then multiply them by each other to get the actual reported time.




              Actual labor minutes: Sum(Transactions.Trans_Qty) * Sum(Labor Hours.Process_Hrs)*60


              Difference back in hours: (If(isnull(laborcost_lesssubassembly),laborcost,laborcost_lesssubassembly))/$9.46)*60) - (Sum(Transactions.Trans_Qty) * Sum(Labor Hours.Process_Hrs)*60) / 60


              Now - I need this data (the difference) to be bar charted by Qtr, Month, Week, Customer, Part, Customer/Part.


              Does this help you?

            • Re: Trouble charting data
              Joe Hutchings

              Does anyone have any suggestions? I cannot get this to chart by just 1 dimension. Only when I have both.


              What would this indicate? If this is a newbie question, I apologize.