2 Replies Latest reply: Nov 18, 2014 10:23 AM by john pillinger RSS

    Count(Interval) not working

      Hi,

       

      Data

      2014-11-17 09:00:00,2014-11-17 08:00:00

      2014-11-17 09:00:00,2014-11-17 07:00:00

      2014-11-17 09:00:00,2014-11-16 08:00:00

      2014-11-17 09:00:00,2014-11-15 07:00:00

       

      Load Script

      LOAD

          @1,

          @2,

           Interval(@1-@2 ,'DD') as "closing_interval_days"

      FROM [lib://Documents/temp_qlik_data.txt]

      (txt, codepage is 1252, no labels, delimiter is ',', msq);

       

      If I add a 'table' to the sheet,  it correctly gives me the following values for the intervals

       

      0

      0

      1

      2

       

      But if I add a pie chart with a count of the intervals I get

       

      0  count 1

      0 count  1

      1  count 1

      2  count 1

       

      I would have hoped for

       

      0 count2

      1 count1

      2 count1

       

       

      Is this correct?   Have I misunderstood interval?  Are the underlying minutes/seconds still there in the background?

       

      Any help appreciated.

       

      John.

        • Re: Count(Interval) not working
          Jonathan Poole

          You are close. The issue is this

           

          Interval(@1-@2 ,'DD') as "closing_interval_days"


          The interval will store all the details (days, hours, minutes, seconds etc..) between the 2 timestamps but the interval() function with 'DD' is just a formatting measure.  So the pie chart believes they are different even if that is not obvious. When you format the day dimension to show more details as below, its apparent

          Capture.PNG.png


          To resolve this replace it with this which will round it to whole integer days and it should work the way you want.


          floor( Interval(@1-@2 ,'DD')) as "closing_interval_days"