Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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"





View solution in original post

2 Replies
JonnyPoole
Employee
Employee

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"





View solution in original post

Not applicable
Author

Fantastic,  thanks for getting me the right answer so quickly.

Appreciated.