Total time for number of records not single record

[joeybird]

Hiya

this code below works great to calculate total time when you select a single customer ID

Interval(Date#([DateOrderCompleted], 'DD/MM/YYYY hh:mm:ss') - Date#([DateOrderReceived], 'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss' )

However ...I wish to calculate the total time for all customers (not single) within a certain department

I have tried adding sum and count, but I get error

any ideas please

kind regards

joeybird

[sunny_talwar]

May be this:

=Interval(Sum(Aggr((Date#([DateOrderCompleted], 'DD/MM/YYYY hh:mm:ss') - Date#([DateOrderReceived], 'DD/MM/YYYY hh:mm:ss')))), 'hh:mm:ss' )

[joeybird]

Hiya

no joy

keep getting error in expression

if I do below

I don't get error but it still wont add up

=Interval(sum(Date#([DateOrderCompleted], 'DD/MM/YYYY hh:mm:ss') - Date#([DateOrderReceived], 'DD/MM/YYYY hh:mm:ss')), 'hh:mm:ss' )

Kind Regards

Joeybird

[sunny_talwar]

May be try this then:

=Interval(Sum(Aggr(Sum(Date#([DateOrderCompleted], 'DD/MM/YYYY hh:mm:ss')) - Sum(Date#([DateOrderReceived], 'DD/MM/YYYY hh:mm:ss')))), 'hh:mm:ss')

[joeybird]

Hiya

still no joy, I have tried removing and adding brackets

it seems to hate the aggr function

kind regards

joeybird

[sunny_talwar]

I am out of my senses today I think, I did not give a Aggrgating field name here.

=Interval(Sum(Aggr(Sum(Date#([DateOrderCompleted], 'DD/MM/YYYY hh:mm:ss')) - Sum(Date#([DateOrderReceived], 'DD/MM/YYYY hh:mm:ss')), DimensionsYouAreAggregatingOn)), 'hh:mm:ss')

Replace the text in Red with your aggregating dimensions.

Best,

Sunny

[joeybird]

Hiya

does not hate the expression this time but

still no joy, I have tried a number of different dimension and still no joy

this is going to be a challenge me thinks

kind regards

joeybird

[joeybird]

Hiya

is there  a way of adding the columns as a value in the data load editor  as (total time)

then in the kpi add up the (total time)

kind regards

joeybird

[joeybird]

Hiya

like the following in the load statement

sum(if(DateOrderCompleted) - (DateOrderReceived) as [Total Amount],

(this is the part I am stuck on)

then I can do a sum[Total Amount], in the kpi

Kind regards

joeybird

[joeybird · Accepted solution]

msolomov                     05-May-2015 10:02          

Remove "if":

sum(DateOrderCompleted - DateOrderReceived) as [TotalNumberOfDays],

thanks Mike ! x

Kind Regards

Joeybird