Tagged with date-time-formatting - Visokio Forums

Date/Time: re- format durations from seconds to minutes?

JfJf — Tue, 20 Nov 2012 10:04:40 -0500

Hi, I am having an issue with the time format. Specially when calculating time spent on the websites, from Google Analytics, I get all the times in seconds when I export the data. I would like to display them in minutes, the problem is that it could be more than a minute, lets say 120 seconds, so when I use the "ss" formatting as an input value and then in the output (as I want it in minutes) I will use "mm".. that is my problem, it is all planned for formatting purposes but not for this type of calculations when it comes to time.

What I am intending to have is to display instead of 150 seconds, it will show up as 02:30, or 00:02:30. I hope it makes sense. Is there a solution for that? I am currently using 2.7 b442.

Reference:
http://www.visokio.com/dates-and-times

Date/Time: Importing from DART and Salesforce issues

cgamolo — Thu, 10 Jan 2013 15:05:33 -0500

Hello,

We are running into a few issues with the date fields from 2 data sources.

DART and DATE
We are connecting to DART and have a DATE field. Based on the Field Organizer, the original format of the DATE comes in as Category. We want to convert this to use a Date/Time format (so that we can run some formulas later on), however, when choosing the Date/Time option in the drop down, it blanks out the entire date entry. I believe we've used this function before, so not sure why this is removing the date entries now.

How can we ensure that a date is read as a date?

SALESFORCE and DATE
We are connecting to Salesforce and also have a DATE field. Based on the Field Organizer, the original format of the DATE comes in as Date/Time. The date format is defined as yyyy-MM-dd and this is displaying properly in the preview. We want to concatenate this date with another column, however, when we do this, the date formatting is being converted to a very long string.

Ex. Start_Date= 2013-01-01
End_Date = 2013-01-31
Campaign_Name = XXX

Formula: [Campaign_Name]+ " ("+[Start_Date]+" - "+[End_Date]+")"

Result: XXXX (Tue Jan 01 00:00:00 EST 2013 - Thu Jan 31 00:00:00 EST 2013)

How can we keep it so that what we concatenate is the same as what is being displayed?

Please let me know if you need additional information to further investigate.

Thanks,
Catherine

Date/Time: 2013 Dates in Week Format?

hgross — Wed, 02 Jan 2013 17:06:57 -0500

I'm having a bit of trouble getting a field organizer to automatically convert the date field into weeks. Instead of calling the last two days of the year the first week of 2013, it's marking them as 2012. Any actual days in 2013 are coming up blank. Screenshot attached.

Any help you can offer would be great. Thanks.

Formulae: Bucketing time values?

DanVatcu — Fri, 14 Dec 2012 15:30:49 -0500

I have a time field that was imported as text in Omniscope (let's call it TIME). The field contains only time without date and the format is HH:mm:ss.

Based on the TIME field , I need a formula to convert it into a new text field (let's call it TIME_OF_THE_DAY) as follows:

If TIME is between 00:00:00 and 06:59:59 then TIME_OF_THE_DAY field value should be 'night'
If TIME is between 07:00:00 and 11:59:59 then TIME_OF_THE_DAY field value should be 'morning'
If TIME is between 12:00:00 and 17:59:59 then TIME_OF_THE_DAY field value should be 'afternoon'
If TIME is between 18:00:00 and 23:59:59 then TIME_OF_THE_DAY field value should be 'evening'

How can I do it? Can you suggest a script?
Thanks

Date/Time: Excel import - dates parsed as numbers?

tommyc — Fri, 30 Nov 2012 10:50:00 -0500

I perform an update each month with newly refreshed data set (.xlsx). I have some column headings that are dates (3/1/2010 going all the way to 9/1/2012). Most of those column headings are parsed correctly as dates, while there are others that are being parsed as numbers (41000, etc). I have checked the source to ensure the columns are formatted as DATE and have even reverted back to the previous version of Omniscope (I just updated it), and it's still happening. Any assistance would be greatly appreciated. Thanks!

Date/Time: Day of week changing?

ALC — Wed, 14 Nov 2012 12:27:08 -0500

I'm trying to build in the day of the week into my report however there seems to be something weird going as the days appear as Sunday, Monday and then Sunday again when a new month starts. I am trying to make the change in a Field Organiser within DataManager

Any suggestions on how I can fix this?

Thanks, Anna

Date/Time: Formatting - input/output -leading zeros?

kristenjberg — Fri, 26 Oct 2012 13:15:26 -0400

My input date format is:
1. Omniscope imported my dates (connected to a MySQL table) as text.
2. I put up the field organizer block and told it that my input format is: mddyyyy
3. I need my output format to be mmddyyyy
4. When I tell the field organizer that this is my output format it puts a leading zero in front of EVERY date, not just those that need the leading zero.

Example:
Input 1: 12141964
Output 1: 12141964 IF I tell the field organizer that the output format is mddyyyy

Input 2: 12141964
Output 2: 012141964 IF I tell the field organizer that the output format is mmddyyyy

So, I need Omniscope to recognize: if field length = 8, then do not put leading zero. if field length = 7, put leading zero.

Do I need a formula to get this result? If so, does anyone have a down-and-dirty one I can use?

Sources: Excel Date formatting?

schergr — Mon, 19 Mar 2012 11:29:28 -0400

I have a spreadsheet with dates formatted as 3/14/2011. However when they come into Omniscope, they are recognized as Date/Time, but they render as 3/14/0011

If I change the date formatting in Excel, I get 3/14/11, but I do want the full four digit year. Am I doing something wrong?

Date/Time: splitting/aggregation/grouping by?

ZhouNan — Thu, 18 Oct 2012 04:48:18 -0400

Hi - I am trying to split/aggregate/group my data on Month Year (e.g. Feb 2012); but the [Date] column is currently also displaying Days (e.g. 17 Feb 2012). Is there any way to achieve this for the view splits; or do I need to create a new formula field/calculated column in the DataManager? If so, which formula is the best for that?

Many thanks!

Idea: Filtering-Date filters in Calendar format?

OmarKhan — Tue, 02 Oct 2012 07:44:20 -0400

I have just a quick question concerning Omniscope – is it somehow possible to have the date filter in form of a calendar? So that it is immediately clear, which day of the week a particular day is, and the number of the week...

I didnt find any such option, but maybe it would be an interesting suggestion to your developers :)

Formula: Date difference (excluding weekends)

rajeshbalu29 — Fri, 07 Sep 2012 05:17:46 -0400

Hi All- I am looking out for a formula for finding the difference between 2 dates but it should not include the weekends.
Example : 07-Sep-2012 - 31-Aug-2012 = 4 Working days. Can anyone please help me on the same?

Thanks & regards - Rajesh B

Formulae: Calculating differences by time periods (WoW, MoM)

Kefsid — Tue, 29 Nov 2011 03:35:00 -0500

Could you please help me if there is anyway to show yesterday's data, last week's same day's data, last month's same day data.. I want to compare this weeks data wth last week's data etc..

Output: Database Tables - Date/Time values (2.8+)

sunicha_sherriff — Thu, 16 Aug 2012 07:16:03 -0400

Hi - At the first,the content is as in picture "Original content" and then I would like to update it to my database using operation "Database Output" and select Publish method: Update data.

But the result is Omniscope change my content that you can see in picture "Result Content" and see at column Platts Created. It changed from "Oct 1, 2010 5:42:26 PM" to "Oct 1, 2010 12.00.00 AM". Omniscope 2.8 build 258(Alpha) *64

Best Regards - Palm

Formulae: Time differences?

dszl136154 — Thu, 23 Aug 2012 12:43:15 -0400

Hi - I attached an Excel File to give you an idea what I would like to do in Omniscope. I have groups, events and time when the events happened. I am trying to calculate the time difference between First and Last event amongst different groups (e.g. A,B) and have result always in the row with First Event. Could you help me with this problem? Thank you

Demo: Business Days Between Two Dates

CRead — Thu, 23 Aug 2012 07:13:10 -0400

**Edited to post an improved method, as I found in some cases the JavaScript gave odd results.**

This formula gives the same result as the NETWORKDAYS() function in Excel for all the dates I tested:

DECLARE(
ve,DECLARE(de,DATETOTEXT([End Date],'E'),IF(de='Mon',0,de='Tue',1,de='Wed',2,de='Thu',3,de='Fri',4,de='Sat',5,6)),
vs,DECLARE(ds,DATETOTEXT([Start Date],'E'),IF(ds='Mon',0,ds='Tue',1,ds='Wed',2,ds='Thu',3,ds='Fri',4,ds='Sat',5,6)),
5*INTFLOOR(DATEDIFF([Start Date],[End Date])/7) + VALUE(MID('1234555512344445123333451222234511112345001234550', 7*vs + ve + 1, 1))
)

Source of inspiration:
http://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates

====== Old version, recommended against! ========
Passing dates as dates into a script can cause odd behaviour in JavaScript. This demonstrates passing them as text, then converting in JavaScript to circumvent those issues.

The result is a calculation of the number of business days between two dates.
The script itself is a copy of:
http://partialclass.blogspot.co.uk/2011/07/calculating-working-days-between-two.html

Formulae: Date difference excluding weekends?

shaji_o — Thu, 23 Aug 2012 01:39:37 -0400

Is there a way to find the number of days between 2 dates, excluding weekends?

Date/Time: Parsing incoming Text with no year?

sherriff — Fri, 20 Jul 2012 11:42:46 -0400

I know that Date/Time handling is one of the most basic operations in Omniscope and that I should be able to do it by now having worked with Omniscope for over a year. However I am having the following problem:

Reading in a .txt file with comma separators, I get a Text field with date/time formatted as follows:

Tue 19 Jun 19:33

I am trying to format this into a recognised Date/time field using the Date format (input):
EEE dd MMM HH:mm
but I see no output? What am I doing wrong?

Bar/Line View: Ordered Dates along Bottom Axi?

schergr — Mon, 23 Jan 2012 17:44:14 -0500

I have a Bar/Line View which along the bottom (split) axis contains a series of dates (data imported from Excel in the date/time format -- 3/14/2012) the dates are not sorting in chronological order and I'm trying to figure out how to ensure that the dates are displayed chronologically.

Any thoughts?

Date/Time: week formatting

mokirk — Thu, 08 Mar 2012 11:53:09 -0500

I have a text field with value 200901 (year followed by week) . When i convert it to date using texttodate(200901 ,"yyyyww"). It does not work and returns an error. When I use a different format it returns 29-Dec-2008 00:00:00. It does not like something about 200901.

Week numbering in Field Organiser

Louise_Peers — Thu, 09 Jun 2011 05:31:31 -0400

I'm finding that '01.01.2010', is coming up as week '52 2011' when I reformat the dd.MM.yyyy original format to ww yyyy in the field organiser. I understand because this is the last full week of the 52 week cycle, but it'll be confusing when we do get to week 52 2011. Is there a way round this?

Date/Time formats: Aggregate/split Date/Time fields by quarters?

naruemon — Fri, 06 Jul 2012 01:04:15 -0400

Do we have the option to aggregate date/time field to be quarterly? - Thanks

Formulae: Convert a time field (hh:mm) to an integer?

JamesDutton — Mon, 02 Jul 2012 21:44:30 -0400

I'm trying to create a formula that takes a time field (in the format hh:mm) and multiplies by a revenue figure it to create a billings model for people. However, in the current format I can't multiply by anything. Makes sense. So, my question is - is it possible to convert this field to an integer (i.e. convert hours & mins to mins) - e.g. 01:30 becomes 90. That 90 would be multiplied by their hourly rate - e.g. $100 = $900 billings.

Date/Time: Cross-time zone publishing fix (2.8+)

steve — Thu, 21 Jun 2012 11:53:40 -0400

Omniscope 2.8, available shortly, fixes a known issue with publishing IOK files with date fields to recipients in different regions.

2.7 and earlier

Dates in Omniscope are modelled as absolute points in time (known as "unix" or "epoch" time) and are displayed in the local time zone in whatever format is configured for the field - e.g. "month-year", stored as the instant at the beginning of the month in question.

Additionally, different regions have different "first day of week" settings, leading to different week number calculations.

Typically this can lead to dates appearing offset by a day, or week histograms starting on the wrong day of the week (Sunday vs. Monday), when an IOK file from 2.7 is opened on a PC in a different region.

Until your publishing and end-user installations have been updated to Omniscope 2.8, you will need to either author the IOK file in the recipient's time zone, or will need to store dates as hidden text fields with a TEXTTODATE formula field configured to recalculate on open.

2.8 and later

As with 2.7, dates continue to be modelled as absolute points in time. But now, IOK files carry the originator's timezone and calendar week settings with them.

Time zone handling

Omniscope 2.8 now captures a fixed time zone when you create a file, which is the system time zone at that point. It also captures the current time zone when you first save a legacy file (from 2.7 or earlier) using 2.8+, although that setting will be lost if you subsequently save in 2.7.

When a time zone-aware file (saved in 2.8+) is subsequently opened, it continues to work in the same originating time zone. It doesn't matter whether you view or edit it on another PC the other side of the world, the time zone is still fixed to the originating time zone.

All date fields are recorded internally, manipulated, and displayed, in that time zone. This means that you'll always see the same date calculations regardless of local system time zone.

Unfortunately this will have no effect for clients still using 2.7 or earlier until they upgrade to 2.8. See workarounds above, meanwhile.

Regional settings dialog

You can use Settings > Advanced file settings > Regional settings to:

Review all regional settings and related.
Change the calendar week settings according to the target audience, if needed.
Change the setting and convert any date fields. Typically you wouldn't normally need to do this; the conversion isn't exhaustive. However, if your file has the wrong setting and has "hidden date offsets" due to being created in a "pre-June-2.8" version of Omniscope, you can correct it.
Diagnose "hidden offsets" (symptoms of a legacy file with the wrong time zone setting).

Calendar week settings

In the same dialog, Omniscope also captures some calendar week settings. This is for a similar reason to time zone. Calendar week settings vary by locale (Saturday/Sunday/Monday as first-day-of-week), yet your formulas must be able to perform week-of-year settings without the local PC settings affecting the result.

As per time zone, these settings are captured and frozen when you create a file, according to the typical regional settings for your PC's locale. The dialog also explains what are normal settings for different locales.

Language and data locale

The pre-existing settings "Language" and "Data locale" still work as before. They are installation settings, which aren't saved as properties of a file. But they no longer have any impact on most date calculations / manipulations / storage. Previously they affected calculations in rare situations.

"Language" means the interface language, and has no effect on data manipulation or display.

"Data locale" means that (by default) if you're in France, you'll see comma for decimal point and French textual month/day names.

Please be aware that if you are using formulae to process numbers or dates inside text values, you should provide an explicit locale ID in the formula function arguments. Without this, textual month/day names, and decimal point / thousand separator characters, will follow the rules of the local installation's "data locale" setting.

Merging different frequency data

anna — Mon, 30 Jan 2012 07:08:23 -0500

Hello - I would like to merge two datasets where one contains daily data (multiple entries in one day), whereas the other file is monthly (single entry each month). Could you advise on the most efficient way to do that? My aim is to transform the daily file into monthly values. I'm planning to use the formula referring to the entries from both files so it's important to obtain the same data frequency beforehand. I would appreciate your help. Thanks Anna

Subtracting the Date/Time fields

rajeshbalu29 — Wed, 16 Nov 2011 05:49:28 -0500

Hi - I need to create a formula for finding the date difference.
=if(Create_date - Start_date < 24hrs,"Yes",NULL)
Can anyone help me on getting the formula for this one please. Thanks in advance

Custom "smart date labelling"

Alk — Wed, 10 Aug 2011 06:04:52 -0400

Further to this discussion (http://forums.visokio.com/discussion/894), it would be good to have custom "smart date labelling" where the user can define the week, month, quarter and year start dates.

I have seen the following threads: http://forums.visokio.com/discussion/604 and http://forums.visokio.com/discussion/910, but they don't resolve the problem of misrepresentation of data when bar-charts with daily data are aggregated into monthly data, as the months shown are calendar months rather than the our corporate calendar months.

Date/Time: Aggregate on date using histograms?

adam_varney — Mon, 22 Aug 2011 11:21:36 -0400

I am trying to aggregate some data based on months and total number of counts. However when trying to use the histogram function in the aggregate operation box and then selecting fixed interval size of 1 month, it is combining all of my dates as 1, with the date of "01-00-2010"?

Not sure if this is a bug or me using the histogram feature incorrectly any advice on this situation would be greatly appreciated.The end result of using the aggregate function should show me only a few months and the totals for each of them as they have not been aggregated but Summed. Regards Adam

Idea: Date/Time - Duration format?

steve — Mon, 15 Aug 2011 05:02:09 -0400

Add ability to show a duration such as "27:15" for 27 hours 15 minutes?

Examples of different formats:

27:15
27 hours 15 minutes
1 day 3:15

Currently you have to use a date field which only works for values that represent points in time (so durations beyond 23:59 are not supported) or a decimal field representing, for example, 27.25 hours.

This could either be an option for date fields, allowing currently 'illegal' date/time elements such as 400 days or 27 hours.

Or as a "show as duration in [days/hours/minutes...]" option for decimal fields, allowing a fractional value such as 27.25 to be formatted as a time.

Disabling or changing buckets in date bucketing - H1, Q1, W1

Alk — Tue, 26 Jul 2011 05:06:49 -0400

Hi, as of 2.6-alpha b699, there has been date bucketing added to Omniscope (experimental).

I've tried to use b713 to see the feature and it simply does not work for my situation, reason being, the H, Q and W I use is different to the conventional calendar H, Q and W - year and week starts at a different date/day and some years have 52 weeks, others, 53.

How do I disable date bucketing for the iok so dates look the same as older builds in b699+?

Date/Time: Formulae for measuring staff performance?

Kefsid — Thu, 07 Jul 2011 14:26:36 -0400

Hi - We have a ticketing system in our company and i am suppossed to measure the staffs performance with 2.6 Omniscope.

In attached document there is a sample you can see..

Question is What the avarage waiting time for each part is (client, staff, partner)?

Is there any way to measure them in Omniscope? In this example there is only 2 unique tickets with several replies but in my database there is a few hundred thousands of unique tickets with tons of replies!! And i must find a way to show the performance of our staff and our partners...

Kefsid