Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to show Avg.

Hi All,

I have field [Create Date Time] and [Close Date Time] i need to calculate Avg time for resolution.

( [Close Date Time] - [Create Date Time] ) / Count(Id)


How can i do this on the script level on the model.

Thanks

Bosc

12 Replies
MK_QSL
MVP
MVP

Data:

Load * From TableName;

Load

     YourCommonFieldToJoin,

     ( [Close Date Time] - [Create Date Time] ) / Count(Id)

Resident Data

Group By YourCommonFieldToJoin;

Not applicable
Author

Thanks but is is not working and message me error in script, provide you the sample data please check with attached file and what is the script for that.

Thanks

Bosc

schlettig
Partner - Creator
Partner - Creator

Hi,

the Excel Columns are not formatted as a timestamp nor date format.

Try the following script:

LOAD

//    Id,

//     State,

     Timestamp([Creation Date Time], 'YYYY-MM-DD HH:mm:ss') as [Creation Date Time],

//     Status,

//     Urgency,

//     Impact,

//     Priority,

//     Region,

//     [Time Remaining],

//     [Time Elapsed],

//     [Total Time],

     Timestamp([Closed Time] , 'YYYY-MM-DD HH:mm:ss') as   [Closed Time],

     Timestamp([Closed Time] , 'YYYY-MM-DD HH:mm:ss') -  Timestamp([Creation Date Time], 'YYYY-MM-DD HH:mm:ss') as Duration

//     F13

FROM

SampleData.xlsx

(ooxml, embedded labels, table is [Main Sheet]);

Best regards,

Christian

robert_mika
Master III
Master III

Try:

t1:

LOAD Id,

     State,

     [Creation Date Time],

     Status,

     Urgency,

     Impact,

     Priority,

     Region,

     [Time Remaining],

     [Time Elapsed],

     [Total Time],

     [Closed Time]-  [Creation Date Time] as TimeBeetwen

FROM

(ooxml, embedded labels, table is [Main Sheet])

//Where len([Closed Time])>0

;

t2:

load

Id,

State,

TimeBeetwen,

TimeBeetwen/count(Id) as Average

Resident t1

group by Id,State,TimeBeetwen;

drop table t1

2015-07-16_090701.jpg

Not applicable
Author

Thanks and how can i create Bar chart with aggregate by Service and Service Category i attached new file.

Thanks ,

Bosc

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

LOAD

*,

Interval( [Close Date Time] - [Create Date Time], 'DD' )  AS DiffDays

FROM DataSource;


Now in front end just juse

Sum(DiffDays)/Count(Id)


Average should not be calculated at script level.


Regards,

Jagan.

Not applicable
Author

Thanks Jagan can you explain why Average is not calculated at script level.

And on the script Interval( [Close Date Time] - [Create Date Time], 'DD' ) if difference in hours and minutes then how to calculated that. Please provide script for this.

Thanks,

Bosc

jagan
Luminary Alumni
Luminary Alumni

In frontend users will select whatever they wish, at that time this average calculation may go wrong, that is why calculating averages in front end is the best option.

Regards,

Jagan.

Not applicable
Author

Thanks so if i want this difference in seconds then how can i go for this.

And if i use by this load

LOAD

*,

Interval( [Close Date Time] - [Create Date Time], 'DD' )  AS DiffDays

FROM DataSource;

Dimension ----> Region

Expresssion ---->  =Sum(DiffDays)/Count(Id)

So here in this what is the unit of the measurement for Y-axis.

Thanks

Bosc