Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
msolomov 05-May-2015 10:02
Remove "if":
sum(DateOrderCompleted - DateOrderReceived) as [TotalNumberOfDays],
thanks Mike ! x
Kind Regards
Joeybird
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' )
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
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')
Hiya
still no joy, I have tried removing and adding brackets
it seems to hate the aggr function
kind regards
joeybird
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
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
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
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
msolomov 05-May-2015 10:02
Remove "if":
sum(DateOrderCompleted - DateOrderReceived) as [TotalNumberOfDays],
thanks Mike ! x
Kind Regards
Joeybird