topic Re: Sum time interval in chart in QlikView

Sum time interval in chart

sifatnabil — Fri, 06 May 2016 09:25:24 GMT

Hi all,

I can successfully calculate the time difference between 2 datetimes for each order using:

Interval(timestamp(max(RemoveTime))-timestamp(min(InsertTime)))

However, I want to sum the total time difference by client. When changing the dimension to client and using this expression, the sum is not correct. What expression should I use? Thanks.

Re: Sum time interval in chart

sunny_talwar — Fri, 06 May 2016 09:27:19 GMT

May be like this:

Interval(Sum(Aggr(Interval(TimeStamp(Max(RemoveTime))-TimeStamp(Min(InsertTime))), OrderID)))

UPDATE:

or this based on the fact that you really don't need TimeStamp function as mentioned by Stefan below

Interval(Sum(Aggr(Max(RemoveTime)-Min(InsertTime), OrderID)))

Re: Sum time interval in chart

rrsrini2907 — Fri, 06 May 2016 09:28:44 GMT

Hi,

Could you post a sample app?

Regards,

Srini.

Re: Sum time interval in chart

miguelbraga — Fri, 06 May 2016 09:29:10 GMT

Hi Sifat,

Can you please share a qvw sample with your issue so that we can see what's you're doing wrong?

Regards,

Re: Sum time interval in chart

miguelbraga — Fri, 06 May 2016 09:33:35 GMT

Even though,

You could make a field in your table like this:

YourTableName:

LOAD *,

Interval(timestamp(max(RemoveTime))-timestamp(min(InsertTime))) as DifTime

Resident YourLoadedTable;

Drop Table YourLoadedTable;

Then you can use this expression in your chart:

Sum(Aggr(DifTime, ClientID))

Regards,

Re: Sum time interval in chart

swuehl — Fri, 06 May 2016 09:34:05 GMT

Just a note, you don't need to format your fields using TimeStamp() when you are doing calculations on them and reformat using Interval() anyway:

=Interval(

Sum( Aggr( Max(RemoveTime) - Min(InsertTime), OrderField))

)

Re: Sum time interval in chart

sifatnabil — Fri, 06 May 2016 10:29:32 GMT

For some reason, this doesn't work. But if we amend it using swuehl‌ 's formula, it works, i.e. by using interval in the outer brackets:

Interval(sum( Aggr( Max(RemoveTime) - Min(InsertTime), ORDER_ID)))

Re: Sum time interval in chart

sifatnabil — Fri, 06 May 2016 10:30:41 GMT

Sent in my reply before I saw your update. Thanks swuehl‌ as well.

Re: Sum time interval in chart

sunny_talwar — Fri, 06 May 2016 10:32:02 GMT

Interval is just a formatting function, I am not sure how this can influence the results? You might be seeing not be seeing the numbers because they are too small, but by adding Interval() or using number tab for formatting, you would end up seeing the same results.