Utilization Rate

[tomhovens]

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.

[vishsaggi]

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

[vishsaggi]

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.

[tomhovens]

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

[vishsaggi]

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.