
Calculation in a Gauge Chart
Sunil Chauhan Aug 29, 2011 10:17 AM (in response to Aissam Boumejjane)probably this will help u
Avg(Count(All{<Destination={'BJS'}>} TransitTime +1))
make BJS in single code

Calculation in a Gauge Chart
Martin Pohl Aug 29, 2011 10:23 AM (in response to Aissam Boumejjane)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

Re: Calculation in a Gauge Chart
Aissam Boumejjane Sep 1, 2011 10:58 AM (in response to Martin Pohl )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

Calculation in a Gauge Chart
Sunil Chauhan Aug 29, 2011 11:27 AM (in response to Aissam Boumejjane)Sum({<Destination={'BJS'}>} TransitTime +1)/Count({<Destination={'BJS'}>}TransitTime)
hope this may help u

Re: Calculation in a Gauge Chart
Aissam Boumejjane Aug 29, 2011 11:40 AM (in response to Sunil Chauhan)Hi,
Thanks for your help! It’s however the same expression as posted before.


Calculation in a Gauge Chart
Martin Pohl Aug 30, 2011 2:28 AM (in response to Aissam Boumejjane)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

Re: Calculation in a Gauge Chart
Aissam Boumejjane Aug 31, 2011 7:29 AM (in response to Martin Pohl )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

Re: Calculation in a Gauge Chart
Martin Pohl Aug 31, 2011 7:35 AM (in response to Aissam Boumejjane)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

Re: Calculation in a Gauge Chart
Aissam Boumejjane Sep 1, 2011 10:58 AM (in response to Martin Pohl )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

Re: Calculation in a Gauge Chart
Aissam Boumejjane Sep 1, 2011 10:37 AM (in response to Martin Pohl )Hi MartinPhol,
I’ve solved the problem J The problem was in the gauge settings and in the formula J.
I really appreciate all your help!!!
Cheers!
iSam




