Calculation in a Gauge Chart

[Anonymous]

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

[SunilChauhan]

probably this will help u

Avg(Count(All{<Destination={'BJS'}>} TransitTime +1))

make BJS in single code

[martinpohl · Accepted solution]

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

[Anonymous]

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

[SunilChauhan]

Sum({<Destination={'BJS'}>} TransitTime +1)/Count({<Destination={'BJS'}>}TransitTime)

hope this may help u

[Anonymous]

Hi,

Thanks for your help! It’s however the same expression as posted before.

[martinpohl]

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

[Anonymous]

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

[martinpohl]

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

[Anonymous]

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