Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I created a gauge chart wherin I'm trying to calculate the average leadtime of all the shipments to Beijing. However my formula does not work
Below is my formula:
Avg(Count(All{<Destination={BJS}>} TransitTime +1))
Suppose I want to express this in percentage, would the below formula work?
Avg(Count(All{<Destination={BJS}>} TransitTime +1)) / Count(Total TransitTime)
Thanks in advance!!!
iSam
Hello iSam,
in your case you calculate the average transittime to Beijing and divide them by the total numbers of all transittime.
Use
Sum({<Destination={BJS}>} TransitTime +1)/Count(({<Destination={BJS}>}TransitTime)
so you add the transittime and devide them by the number of transits to Beijing.
Regards
probably this will help u
Avg(Count(All{<Destination={'BJS'}>} TransitTime +1))
make BJS in single code
Hello iSam,
in your case you calculate the average transittime to Beijing and divide them by the total numbers of all transittime.
Use
Sum({<Destination={BJS}>} TransitTime +1)/Count(({<Destination={BJS}>}TransitTime)
so you add the transittime and devide them by the number of transits to Beijing.
Regards
Hi Martin,
Thanks!! It works!! However when I try to express this in percentage I get the wrong number:
Do I need to add something else to the formula?: Sum({<Destination={BJS}>} TransitTime +1)/Count(({<Destination={BJS}>}TransitTime)
800% should be 100% max.
Thanks in advance!!!
Cheers
isAm
Sum({<Destination={'BJS'}>} TransitTime +1)/Count({<Destination={'BJS'}>}TransitTime)
hope this may help u
Hi,
Thanks for your help! It’s however the same expression as posted before.
hm,
let me calculate.
for example 500 minutes transit time devide by 100 transports = 5 min average.
This is the result of your formula.
What is to be shown in percentage?
Regards
Hi,
If I look at it from my perspective I would say that for example the maximum amount of days should not exceed 7 days. Since the average is 5 days, I would divide 5 by 7 and multiply it with 100 %. That means that approximately 71 percent is on time.
And your example J
for example 500 minutes transit time divide by 100 transports = 5 min average.
This is the result of your formula.
What is to be shown in percentage? The percentage should be 20 percent J
Cheers!
iSam
So 20 percent of my example is
1/(Sum({<Destination={BJS}>} TransitTime +1)/Count(({<Destination={BJS}>}TransitTime))
=1/(500/100)=1/5=0.2
And in your example use
(Sum({<Destination={BJS}>} TransitTime +1)/Count(({<Destination={BJS}>}TransitTime))/7
=(500/100)/7=5/7=0.71
Both shown as percentage in the number options
Regards
Hi,
Unfortunately it does not work L. I think there’s something fishy about my formula or script, I’ve splitted the formula to understand it better:
Sum({<Destination={BJS}>} TransitTime), I get the total transittime of 881.
Now there is the first problem. I should add an extra day to the transittime, but when I do that, Qlikview goes crazy
So I had to remove the “+1” to get the below sum result.
The total shipments is ok.
So if I do Sum({<Destination={BJS}>} TransitTime)/Count({<Destination={BJS}>} Destination) ( 881 / 235) = 3.74 days
Since the maximum days should not exceed 8. I divide it by 8:
(Sum({<Destination={BJS}>} TransitTime)/Count({<Destination={BJS}>} Destination) /8
So now I divide 3.74/8=0.4675. average of 47%. The calculation maybe correct, but since there are not more than 3 shipments with delay, the average on time should be above 95%.
What am I doing wrong? L Should I count only the shipments that are late and divide them by the total shipments?
Thanks for all your help so far!!!!
isam