Utilization in Qlikview

[tomhovens]

Hey Qlikviewer,

Can anybody help me to calcule the utilization of the inventory, see the tabel

Ordernr	Itemnr	Quantity	StartDate	EndDate	Utilization
1	001	10	24/11/2014	27/11/2014	40
1	002	5	24/11/2014	27/11/2014	20
2	001	5	21/11/2014	25/11/2014	25
2	002	15	21/11/2014	25/11/2014	75
3	001	1	25/11/2014	01/12/2014	7
3	002	2	25/11/2014	01/12/2014	14

I made a selection of the order between 22/11/2014 and 28/11/2014 but the utilization must calculate with these dates.

Ordernr 1 is good

Ordernr 2 the StartDate has to be 22/11/2014

Ordernr 3 the EndDate has to be 28/11/2014

Who can help me?

[syukyo_zhu]

you can try to use if.

sum(if(Ordernr=1, quantity, if(Ordernr=2 and StartDate='22/11/2014', quantity, if(Ordernr=3 and EndDate='28/11/2014',0)))

[tomhovens]

The table is an example, total order is 10.000 with different startdate and enddate

[sushil353]

Hi,

Store your start and end date in separate variables:

then in dimension of your chart you can use if condition:

if(startdate> $(vStartDate) and EndDate < $(EndDate),yourDimension,null())

check the option for suppress null values.

HTH
Sushil

[Peter_Cammaert]

Or with set analysis:

=sum({<StartDate = {">=$(vStartDate)"}, EndDate = {"<=$(vEndDate)"}>} Quantity)

[whiteline]

Hi.

Could you explain the result that you are trying to achieve ?

How should the StartDate/EndDate of each row contribute to the result depending on the selected period ?

[tomhovens]

Hey,

the result has to be

Ordnr     Itemnr     Utilization

1          001          40

1          002          20

2          001          20

2          002          60

3          001          4

3          002          8

[#8]

Hey Tom, I built a pretty big asset utilization app to basically solve the same problem.  The function I relied on most heavily was Qlikview's "IntervalMatch" which essentially takes check-in and check-out dates and creates a table of all the days where an item or asset was "out".  You can then sum that field for various usage and utilization stats.

Here is an example app using intervalmatch which really helped me get started:

Dropbox - Count-Days-in-a-Transaction-Using-IntervalMatch.qvw

To get utilization, I select assets I care about, then write expressions to sum the "RentDayCounter" and divide by the number of dates selected.

Hope this helps,

Brian