topic Re: Utilization Rate in QlikView

Utilization Rate

tomhovens — Thu, 09 Mar 2017 21:37:16 GMT

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

vishsaggi — Thu, 09 Mar 2017 23:24:31 GMT

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

Re: Utilization Rate

vishsaggi — Fri, 10 Mar 2017 00:53:45 GMT

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

tomhovens — Fri, 10 Mar 2017 07:01:29 GMT

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

vishsaggi — Fri, 10 Mar 2017 14:28:52 GMT

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.