Sum time interval in chart

[sifatnabil]

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.

[sunny_talwar · Accepted solution]

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)))

[rrsrini2907]

Hi,

Could you post a sample app?

Regards,

Srini.

[miguelbraga]

Hi Sifat,

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

Regards,

MB

[miguelbraga]

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,

MB

[swuehl]

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))

)

[sifatnabil]

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)))

[sifatnabil]

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

[sunny_talwar]

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.