10 Replies Latest reply: Sep 1, 2011 10:37 AM by Aissam Boumejjane RSS

    Calculation in a Gauge Chart

    Aissam Boumejjane

      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
          Sunil Chauhan

          probably this will help u

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

           

          make BJS in single code

          • Calculation in a Gauge Chart
            Martin Pohl

            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

                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

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

                     

                    hope this may help u

                    • Calculation in a Gauge Chart
                      Martin Pohl

                      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

                          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

                              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

                                  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

                                    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