Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

joeybird
Contributor II

Total time for number of records not single record

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

1 Solution

Accepted Solutions
joeybird
Contributor II

Re: Total time for number of records not single record

Remove "if":

sum(DateOrderCompleted - DateOrderReceived) as [TotalNumberOfDays],

thanks Mike ! x

Kind Regards

Joeybird

9 Replies
MVP
MVP

Re: Total time for number of records not single record

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
Contributor II

Re: Total time for number of records not single record

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

MVP
MVP

Re: Total time for number of records not single record

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
Contributor II

Re: Total time for number of records not single record

Hiya

still no joy, I have tried removing and adding brackets

it seems to hate the aggr function

kind regards

joeybird

MVP
MVP

Re: Total time for number of records not single record

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
Contributor II

Re: Total time for number of records not single record


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
Contributor II

Re: Total time for number of records not single record

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
Contributor II

Re: Total time for number of records not single record

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
Contributor II

Re: Total time for number of records not single record

Remove "if":

sum(DateOrderCompleted - DateOrderReceived) as [TotalNumberOfDays],

thanks Mike ! x

Kind Regards

Joeybird