How to show Avg.

[#1]

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

[MK_QSL]

Data:

Load * From TableName;

Load

     YourCommonFieldToJoin,

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

Resident Data

Group By YourCommonFieldToJoin;

[#3]

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]

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]

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

[#6]

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

Thanks ,

Bosc

[jagan]

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.

[#8]

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]

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.

[#10]

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