6 Replies Latest reply: Aug 9, 2011 5:32 AM by Kaushik Solanki RSS

    New to Qlikview: Calculating lean time

    Aissam Boumejjane

      Hi,

       

      Im trying to calculate the lean time between the a departure time and dop. Unfortunatelly I don't know how I can calculate this in Qlikview.

      What expression should I use to calculate this?

       

      I've attached the qlikview document.

       

      Thanks in advance!

       

      iSam

        • Re: New to Qlikview: Calculating lean time
          Kaushik Solanki

          Hi,

           

             Have a look at the rar file.

           

          Regards,

          Kaushik Solanki

            • Re: New to Qlikview: Calculating lean time
              Aissam Boumejjane

              Hi Kaushik,

               

              Sorry for the late reply. The formula you made works!

              Now I have to figure out how to present that data in a chart.

               

              thanks!

               

              Aissam

              • Re: New to Qlikview: Calculating lean time
                Aissam Boumejjane

                Hi Kaushik,

                 

                The expression you send me works, however i'm trying to understand the expression so I can formulated them myself. i'm still new to qlikview

                 

                Interval(date(date#(POD,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm') -      date(date#(DEP,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm'),'d hh:mm') as DEP_POD

                 

                Looking at the first line, why does date appears twice? Interval(date(date#(.........

                Same goed for (POD,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm')  why did you write twice?

                 

                Second, why is it that DEP ends with 'd hh:mm' and POD with ' hh:mm'

                 

                Hope you can help me understand the above formula.

                 

                Thanks in advance!!

                 

                iSam

                  • Re: New to Qlikview: Calculating lean time
                    Kaushik Solanki

                    Hi,

                     

                       Let me explain the formula.

                     

                       Here is the formula

                     

                       Interval(date(date#(POD,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm') -      date(date#(DEP,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm'),'d hh:mm') as DEP_POD

                     

                       As per your requirement you wanted to have a time difference.

                     

                       What i observed from the data which you sent is that the date coming to Qlikview is in string format, So we need to specify Qlikview that the date coming is not a string its a DATE. So for that purpose we need

                      

                       date(date#(POD,'DD-MM-YYYY hh:mm'),'DD-MM-YYYY hh:mm')   Formula.

                     

                       Now this formula says that Give me date(i.e Date() function) where the date coming is in Date Format (i.e Date#())

                     

                       So in Date#() Function we said that the field which contains the date. i.e POD in above expression and the format in which it is. and then as a part of a date function we specified the updated format. As in this case both are the same.

                     

                       Same like for the DEP.

                     

                       Now 'd hh:mm' is used as a parameter to interval function. This means that give me difference in this format. i.e DAY Hour:Minute.

                     

                        Hope you understood.

                     

                    Regards,

                    Kaushik Solanki