4 Replies Latest reply: Mar 10, 2017 9:30 AM by Vishwarath Nagaraju RSS

    Utilization Rate

    Tom Hovens

      Dear Qlikers,

       

      I'm looking for a function to calculate to utilization of an Item. The table I have is enclosed, the columns in there are:

      - ContractID

      - Status                    calculate only when value is even or higher than 4

      - WarehouseID

      - PlanStartDate          start of the project

      - PlanEndDate          end of the project (always +1 for that day)

      - ItemID

      - Quantity

       

       

      The function has a dimension of MonthYear (Januari 2017) and ItemID, please see table Output, and has to summarize the Quantity when;

      - Status => 4 (cells in red)

      - summarize only the days in the month (cells in blue)

      - per WarehouseID

       

      Till now I have a function with multiple aggr's which isn't functioning very well.

        • Re: Utilization Rate
          Vishwarath Nagaraju

          can you explain the expression how you got 219, 160, 112 etc? Need more clarity please...

            • Re: Utilization Rate
              Vishwarath Nagaraju

              And your planenddate for warehouseID4 is not matching with the one in blue. Using filters in excel check for warehouseid 4 and you can see planenddate in big table says 2/10/2017 and the one beside that it says 1/31/2017? Quite did not get if that is right.

               

              Apart form that, i believe this is what you are looking for. Attaching the file.

               

              Your dates look little off for me. Can you provide some revised version of your data, if this is not what you want.

            • Re: Utilization Rate
              Tom Hovens

              It only have to calculate per month like January2017, February2017, March2017 etc.

               

              That why I created the middle table, the function you have I use (little different) but thats costs a lot of time when calculating

                • Re: Utilization Rate
                  Vishwarath Nagaraju

                  The middle table with PlanEndDate's (in Blue for warehouse ID 4 and 8 i believe) not matching with the left table? Which is base table to look at?

                   

                  And your ouput table is not clear, can you populate more data and send us across and exact output values you want to see in your output table.