Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (2)
12 Replies

Re: How to show Avg.

Data:

Load * From TableName;

Load

     YourCommonFieldToJoin,

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

Resident Data

Group By YourCommonFieldToJoin;

Not applicable

Re: How to show Avg.

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
Contributor

Re: How to show Avg.

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

Re: How to show Avg.

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

Re: How to show Avg.

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

Thanks ,

Bosc

MVP
MVP

Re: How to show Avg.

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

Re: How to show Avg.

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

MVP
MVP

Re: How to show Avg.

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

Re: How to show Avg.

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

Community Browser