New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor

can't calculate time difference correctly

Hello everyone,

I'm trying to calculate how many hours my servers have worked properly in march 2018.

For example for id 1 now I have the following:

idstart datehow many months should worktermination datedelta timestate
101-03-2017 10:19:091201-03-2018 10:19:091487:59:594
101-03-2017 10:19:241201-03-2018 10:19:241487:59:594
101-03-2018 10:19:091201-03-2019 10:19:091487:59:593
101-03-2018 10:19:241201-03-2019 10:19:241487:59:593

Where originally I have: ID, start date, how many months should work, state.

Termination date is calculated as

date(AddMonths(start_date, How_many_months_should_work), 'DD-MM-YYYY hh:mm:ss'), so I just add the quantity of months to a start date

And delta time is calculated in chart script as:

aggr(sum(if(interval(start_date) >= interval(MonthStart('01-03-2018')) and interval(Addmonths(start date, How_many_months_should_work)) >= interval(MonthEnd('2018-03-01')) and interval(start_date) <= interval(MonthEnd('01-03-2018')),

interval(MonthEnd('01-03-2018') - start_date), //starts somewhere in march

if(interval(start_date) <= interval(MonthStart('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) <= interval(MonthEnd('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) >= interval(MonthStart('01-03-2018')),

interval(Addmonths(start_date, How_many_months_should_work) - MonthStart('01-03-2018')), //starts before march, closed somewhere in march

if(interval(start_date) <= interval(MonthStart('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) >= interval(MonthEnd('01-03-2018')),

interval(MonthEnd('01-03-2018') - MonthStart('01-03-2018')))))), id) //full month

Some explainations to the code. As I have a time series and I want to calculate hours only for March, there are 3 cases:

1. my date starts in march and goes beyond this month. So delta time is monthend(march) - startdate

2. starts before and ends in march. delta time = termination date - monthstart(march)

3. It goes over the whole march(my case), so deltatime = monthend(march) - monthstart(march)

What I expect to reach is:

idstart dateHow many hours should worktermination datedelta timestate
101-03-2017 10:19:091201-03-2018 10:19:09743:59:594
101-03-2018 10:19:091203-03-2019 10:19:09743:59:593

The main problem is that my algorithm calculates time differences for all 4 states(two states with 4, two with 3), while I need to take the minimum date by state and sum them.
Hope I explained it clearly.

Thanks in advance 3 Replies
Contributor II

Re: can't calculate time difference correctly

What if you add ,state after , id. I've a similar problem today (asked here) and got that answer (and worked!)

New Contributor

Re: can't calculate time difference correctly

sorry, but I didn't understand what you mean. Could you, please, explain it a bit clearly?

Contributor II

Re: can't calculate time difference correctly

I'd try this:

aggr(sum(if(interval(start_date) >= interval(MonthStart('01-03-2018')) and interval(Addmonths(start date, How_many_months_should_work)) >= interval(MonthEnd('2018-03-01')) and interval(start_date) <= interval(MonthEnd('01-03-2018')),

interval(MonthEnd('01-03-2018') - start_date), //starts somewhere in march

if(interval(start_date) <= interval(MonthStart('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) <= interval(MonthEnd('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) >= interval(MonthStart('01-03-2018')),

interval(Addmonths(start_date, How_many_months_should_work) - MonthStart('01-03-2018')), //starts before march, closed somewhere in march

if(interval(start_date) <= interval(MonthStart('01-03-2018')) and interval(Addmonths(start_date, How_many_months_should_work)) >= interval(MonthEnd('01-03-2018')),

interval(MonthEnd('01-03-2018') - MonthStart('01-03-2018')))))), id, state)

Aggr on a graph - incorrect values