topic Re: Utilization in Qlikview in QlikView

Utilization in Qlikview

tomhovens — Thu, 04 Dec 2014 10:40:29 GMT

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?

Re: Utilization in Qlikview

syukyo_zhu — Thu, 04 Dec 2014 10:46:08 GMT

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)))

Re: Utilization in Qlikview

tomhovens — Thu, 04 Dec 2014 10:48:14 GMT

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

Re: Utilization in Qlikview

sushil353 — Thu, 04 Dec 2014 10:48:27 GMT

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

Re: Utilization in Qlikview

Peter_Cammaert — Thu, 04 Dec 2014 11:11:40 GMT

Or with set analysis:

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

Re: Utilization in Qlikview

whiteline — Thu, 04 Dec 2014 21:33:38 GMT

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 ?

Re: Utilization in Qlikview

tomhovens — Fri, 05 Dec 2014 06:47:05 GMT

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

Re: Utilization in Qlikview

Fri, 05 Dec 2014 18:56:43 GMT

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