topic Re: How to show Avg. in Visualization and Usability

How to show Avg.

Thu, 16 Jul 2015 06:20:10 GMT

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

Re: How to show Avg.

MK_QSL — Thu, 16 Jul 2015 06:22:39 GMT

Data:

Load * From TableName;

Load

YourCommonFieldToJoin,

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

Resident Data

Group By YourCommonFieldToJoin;

Re: How to show Avg.

Thu, 16 Jul 2015 07:09:03 GMT

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

Re: How to show Avg.

schlettig — Thu, 16 Jul 2015 07:44:25 GMT

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.

robert_mika — Thu, 16 Jul 2015 08:07:49 GMT

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

Re: How to show Avg.

Thu, 16 Jul 2015 09:16:08 GMT

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

Thanks ,

Bosc

Re: How to show Avg.

jagan — Thu, 16 Jul 2015 10:06:23 GMT

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.

Re: How to show Avg.

Thu, 16 Jul 2015 12:10:24 GMT

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

Re: How to show Avg.

jagan — Sat, 18 Jul 2015 00:26:33 GMT

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.

Re: How to show Avg.

Thu, 23 Jul 2015 10:40:33 GMT

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

Re: How to show Avg.

Thu, 23 Jul 2015 10:40:54 GMT

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

Thanks

Bosc

Re: How to show Avg.

Fri, 24 Jul 2015 05:27:45 GMT

Please reply jagan on this.

Bosc

Re: How to show Avg.

jagan — Mon, 27 Jul 2015 03:00:59 GMT

Just give SS instead of DD.

LOAD

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

FROM DataSource;

Regards,

Jagan.