Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)))
The table is an example, total order is 10.000 with different startdate and enddate
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
Or with set analysis:
=sum({<StartDate = {">=$(vStartDate)"}, EndDate = {"<=$(vEndDate)"}>} Quantity)
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 ?
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
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