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.

       

      (((((If(Isnull(LABOR_LESS_SUBASSY_COST),LABORCOST,LABOR_LESS_SUBASSY_COST)/9.46)*60)) * Sum(TRANS_QUAN))-(((SUM(PROCESS_HOURS) * 60) / (SUM(TRANS_QUAN)))* Sum(TRANS_QUAN)))/60

       

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

       

      Thanks!

        • 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

          Chris

            • 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:

               

              If(isnull(laborcost_lesssubassembly),laborcost,laborcost_lesssubassembly))/$9.46)*60

               

              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.

               

              So:

               

              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.