Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted
Creator II
Creator II

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

Highlighted
Contributor
Contributor

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

Highlighted
Creator II
Creator II

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