turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- Summarizing time columns in the VA

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-03-2016 03:20 PM

Time and date columns are treated as categories , hence summrization is not possible. The only solution is convert the time column into numeric and use them as measure and get summary total. but the total look useless because its all numeric values, i want to see total in hh:mm format.

I'm trying to change the format of time column to elapsed time and summarizing them in the bottom. The other reporting tool allows summarizing of time column, not sure why it's not supported in SAS . Is there a work around??

eg

time

03:16

04:16

05:16

______

sum 12:48

Accepted Solutions

Solution

10-06-2016
05:02 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-04-2016 02:56 PM

Hi

yes a time value is a category value and therefor you can not do any aggregations.

I suggest to do the follwoing:

create a new calculated data item that represents the time as a numeric value using the TreatAs operator, something like:

`TreatAs(_Number_, 'Time'n)`

Then you create a new aggreated measure using this expression:

`Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 )`

where 'timeSec'n is the calculated item from the first step

Essentialy you compute the minutes from you seconds and then use the Mod operator to add the remaining minutes (divided by 100) to the result. This will give you the proper result as mm.ss,, see also picture

Give it a try

Bruno

All Replies

Solution

10-06-2016
05:02 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-04-2016 02:56 PM

Hi

yes a time value is a category value and therefor you can not do any aggregations.

I suggest to do the follwoing:

create a new calculated data item that represents the time as a numeric value using the TreatAs operator, something like:

`TreatAs(_Number_, 'Time'n)`

Then you create a new aggreated measure using this expression:

`Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 )`

where 'timeSec'n is the calculated item from the first step

Essentialy you compute the minutes from you seconds and then use the Mod operator to add the remaining minutes (divided by 100) to the result. This will give you the proper result as mm.ss,, see also picture

Give it a try

Bruno

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-04-2016 05:44 PM - edited 10-04-2016 05:45 PM

Thanks Bruno,

I see it produce results in mm:ss, is there way to include hh:mm:ss as well, i have seconds in hours as well. like timesec = 4284 and i want to convert into 1:11:24 and summarize in bottom

Thanks for your input

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2016 01:14 AM

One could use the same technique for hours as well, but I guess the formatting of the result will be difficult, since there is no numeric format that with ":" as separator

Bruno

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-05-2016 11:41 AM

Hey Bruno, thanks for your input. this is what i use to convert sec into min and min into hr.

hourMinMsr=

If Sum [_ByGroup_] ('timeSec'n) <=3600 return

Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 )

else

(Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 ))/60

The above scenario would display both sec in hours and min but user would have difficulty interpreting the data and differentiating between min and hours. To represent the data accurately, i think we can only use sec in min or sec in hour to accurately display the results. To combine sec in both min or hr will lead to more confusion. Let me know if you think otherwise or better solution. thanks.

TimeCat timesecNum MinMsr hourMsr hourMinMsr

0:06:04 364 6.04 0.10 6.04

1.11.24 4284 71.24 1.19 1.19