topic Re: Calculation in a Gauge Chart in QlikView

Calculation in a Gauge Chart

Anonymous — Mon, 29 Aug 2011 14:13:30 GMT

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

Calculation in a Gauge Chart

SunilChauhan — Mon, 29 Aug 2011 14:17:20 GMT

probably this will help u

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

make BJS in single code

Calculation in a Gauge Chart

martinpohl — Mon, 29 Aug 2011 14:23:03 GMT

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

Anonymous — Mon, 29 Aug 2011 15:13:18 GMT

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

SunilChauhan — Mon, 29 Aug 2011 15:27:18 GMT

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

hope this may help u

Re: Calculation in a Gauge Chart

Anonymous — Mon, 29 Aug 2011 15:40:57 GMT

Hi,

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

Calculation in a Gauge Chart

martinpohl — Tue, 30 Aug 2011 06:28:12 GMT

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

Anonymous — Wed, 31 Aug 2011 11:29:38 GMT

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

martinpohl — Wed, 31 Aug 2011 11:35:12 GMT

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

Anonymous — Thu, 01 Sep 2011 09:20:29 GMT

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

Anonymous — Thu, 01 Sep 2011 14:37:41 GMT

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