Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

tomhovens
New Contributor II

Utilization in Qlikview

Hey Qlikviewer,

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

OrdernrItemnrQuantityStartDateEndDateUtilization
10011024/11/201427/11/201440
1002524/11/201427/11/201420
2001521/11/201425/11/201425
20021521/11/201425/11/201475
3001125/11/201401/12/20147
3002225/11/201401/12/201414

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?

7 Replies
syukyo_zhu
Contributor III

Re: Utilization in Qlikview

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
New Contributor II

Re: Utilization in Qlikview

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

sushil353
Honored Contributor II

Re: Utilization in Qlikview

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

Or with set analysis:

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

whiteline
Honored Contributor II

Re: Utilization in Qlikview

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
New Contributor II

Re: Utilization in Qlikview

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

Not applicable

Re: Utilization in Qlikview

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

Community Browser