Tagged with date-time-formatting - Visokio Forums

Date and time formatting: Times changing?

kim2604 — Tue, 18 Jul 2017 14:54:25 -0400

Omniscope is changing the date from what is in the database to what it is appearing in Omniscope. Two days ago the difference was 2.5hrs and today the difference was 1 day. We are using the system locale English United States setting which is the same as our DB. Because the date difference is not consistent, it is hard to understand what is going on.

Date/ week calculation: Date minus an integer

craven99 — Wed, 24 Aug 2016 07:40:38 -0400

Hi,

I have a week field - which was duplicated from a standard date field and works fine. But our payroll team work on a back week so they wish the filter to be the current displayed week minus 1 week. So Omniscope is correctly stating week 34 - but my team need it to show week 33. Can for the life of me figure out how to get the field to show this - am I missing something simple?

Demo: Dynamic calculation of investments with different maturity dates

paola — Tue, 17 Nov 2015 07:16:46 -0500

Simple demo attached, illustrating the use of DATEDIFF formula in the scenario where investments/deposits have different maturity date.
First step is calculation of number of days between today and the investment maturity date:

DATEDIFF(NOW, [Date], "days")

By using NOW function, this calculation will keep the report up-to-date, always returning the latest breakdown.
It is also possible to 'fix' the date and use DATEVALUE("dd/MM/yyyy") instead of NOW, e.g. DATEDIFF(DATEVALUE("01/10/2015"), [Date], "days") .
Second step is to create irregular time intervals, that will 'bucket' the records and be used for sum calculation.


IF(
[Difference days]<1,"matured",
[Difference days]<2,"1 day",
[Difference days]<8,"1 week",
[Difference days]=<31,"1 month",
[Difference days]<92,"3 months",
[Difference days]<184,"6 months",
[Difference days]<366,"1 year",
[Difference days]<1096,"3 years",
[Difference days]<1827,"5 years",
[Difference days]<2557,"7 years",
[Difference days]=<3653,"10 years",
[Difference days]>3653,"over 10 years",
null)

Aggregation and summing of investment amounts for each period is done in the Bar/Line view.

Date/Time: Upcoming Leap Second - No effect on Omniscope use

paola — Mon, 22 Jun 2015 07:16:58 -0400

A leap second is typically perceived by the application no differently to periodic synchronisation (i.e. correction) of the computer's clock by a small amount.

Omniscope is tolerant of much greater system clock changes than one second. Additionally it is a Java based application and the current versions of Omniscope use a recent version of Java, which has been patched to include fixes for leap seconds on all supported platforms.

Date/Time: DateToText Function, using "yyyy" and not "YYYY"

benjamin — Wed, 25 Mar 2015 14:08:27 -0400

Hi,
Using the DatetoText function results in errors when you use dates near the end of the year (anywhere from the the 29th-31st of Dec)
Example :
DATETOTEXT(date(2013,12,31),"YYYY")

Thanks, Ben

Scheduler: PDF Batch date/time error?

Alexander — Wed, 22 Jan 2014 10:23:00 -0500

All,

I get an error in one of our schedulers. The error is "DUMP BUG HANDLER Header Foutrapport Message Unable to re-parse: time -262800000 s 196901 f yyyyww"

This causes the batch output to skip files that needed to be generated. All PDF files that need to be published in this tasks are skipped.

Please advise asap.

Thanks!
Alexander

Bar/Line View: Display of last weekly split by Date/Time field?

rajeshbalu29 — Thu, 04 Dec 2014 14:31:02 -0500

Hi Team,
I have found that there seems to be a display issue when we use the date field in the split for 1 week which shows for the week starting 30th Nov 2014 to 6th Dec 2014. It is currently showing the label "30 to 6 Nov, 2014" which really should be showing "30 to 6 Dec, 2014"?

Please find the attached screenshot and let me know if you have a fix for this in any build.

Note: Currently I'm using the latest stable build for Visokio 2.8 b1287.

Date/Time: How to display day of week?

cristian — Fri, 19 Dec 2014 10:38:57 -0500

Hi All!
I want to create a chart that has days 1-31 on the x-axis. Aggregation is on sales amount. I have a table with transactions for each day for a complete year and I want to show the sales per day regardless of month. Is there a date function that extracts the day of the date? I tried to use the Expand Values block but I can't choose my date field in "Field selection" of the block. Any suggestions?

Br
Cristian

Formulae: Identify date/time in the next 60 minutes

nash — Mon, 24 Nov 2014 04:15:48 -0500

Hi, I have a date/time column. I would like to find out if there have been any other date/time within the next 60 minutes:

Date Result
15.05.2014 15:05:55 no
15.05.2014 17:10:55 yes
15.05.2014 17:20:55 no

I tried a similar logic as for moving average, but it didn’t work. I am not sure why. Maybe the date/time format works differently.

Formulae: Date/Time format for cumulative hours beyond 24?

aozols — Mon, 10 Nov 2014 15:49:12 -0500

I'm pulling in Google Analytics data and attempting to create a formula to calculate average session duration (total session duration/sessions).

The issue that I'm running into is when summing my "total session duration" date field. The HH:mm:ss format works up until the hour parameter hits 24 (as the setting for HH is a 24-hour clock). Is there a way to get around this 24-hour restriction? Should I be using a different format?

Example attached.

Date/Time: Calculating and comparing durations?

chrisamott — Tue, 05 Aug 2014 11:27:44 -0400

I have an Access import data file which contains three fields. A start date/time an end date/time and a Time Period. The Time Period is the duration between the start and the end. My task is to confirm the Time Period is correct.

I have used the DateDiff function to calculate the difference between Start and End but it produces a decimal number. I have tried many different formulae and reformatting measures to compare the Time Period with my calcualtion but they all fail.

Either Omniscope cannot parse the decimal or it doesn't recognise the time to convert it to text. I can easily paste the Time Period into Excel and reformat as a general number and it produces a decimal that compares favourably with my DateDiff but I need to do this in Omniscope.

Date/Time: Custom "smart labelling"

nash — Wed, 16 Jul 2014 04:57:56 -0400

My data have a data field with the years 2011, 2012, 2013 on a monthly base (Jan - Dec) and the first six months in 2014.

I want to show the spendings on the year base in the bar/line view, so I split on the date field and take the interval “1 Year”.
The result is that the bars are labeled by year and it is not clear that the year 2014 is not a full year.

My question is, if a custom labeling is possible? For example "2014 ytd" or Min and Max Values of this year: "01.2014-06.2014"

Thanks

Date/Time: inconsistent formats in a Date field

paola — Mon, 23 Jun 2014 12:57:31 -0400

Attached file deals with issue of inconsistent date formats in a single field, where few extra steps are needed to bring all the values to the same format, including use of TEXTTODATE formula.

Another useful example :
4/18/2015 1:31:09.000000 AM
translates into Omniscope format
M/d/yyyy h:mm:ss.SSS a

Export: Date/time formats to Excel on a new PC?

Panos — Tue, 17 Jun 2014 12:55:28 -0400

Hi

I face some issues with the date format. While all the dates are formatted properly on my Omniscope final box (dd/mm/yyyy), when I publish the file in Excel format, some of the dates change on text format (mm/dd/yyyy hh:mm:ss) and the rest are still on date format (mm/dd/yyyy).

I started to face that issues since I changed my PC, I am telling that just in case there is an option I have to change.

Thanks, Panos

Date/Time Format - Convert month name to number?

Valentina — Wed, 11 Jun 2014 16:08:33 -0400

Hi,

I have a Date column which returns the dates in the following format: Mon May 26, 2014.
I want to convert this which is a text format to a date format. What makes it difficult is the month which is represented as text.

Is it possible to do this using a formula?

Thanks in advance

Sorting: Mondays first in Table View?

Panos — Thu, 29 May 2014 08:01:07 -0400

I have converted my date fields to display as week days by using format "EEEE", and now I would like to show them on a Table view sorted by week day. But even if they are in the correct order, I can't manage to set my first row as Monday. Could you please help me how to set my first row as Monday and all the rest days sorted on the right order?

Thanks, Panos

Display: Localisation/field formatting for browser versions (2.9+)

Peter — Fri, 23 May 2014 09:48:49 -0400

Localisation and field formatting is now available throughout the mobile application. Date and number values are formatted throughout the app based on the set up data locale.

Fields with dates and numbers are formatted according to the field settings and timezone in the IOK file.
Abbreviations are now more accurate throughout the application.

Date/Time: How to convert Unix timestamp?

naruemon — Tue, 17 Dec 2013 03:53:07 -0500

In Source file, there has the Unix timestamp which I would like to convert to be datetime format.
Even I did as suggested in below link:
http://www.visokio.com/dates-and-times

It could show datetime but still not valid:
Ex: Unix Timestamp 1387265894
Follow the link the result will be: Jan 17, 1970 8:21:05 AM
Actual value should be : Tue, 17 Dec 2013 07:38:14 GMT

Moreover, when followed the method as suggested in the link, in 'Field Organizer' the data type still be integer. So we could not use this value to do the calculate in any datetime functions.

Version: Omniscope 2.9 build 699.

Pivot View: Date & Time Fields and Top 5 Text Categories.

rajeshbalu29 — Fri, 27 Apr 2012 01:53:12 -0400

Hi All,

I have a requirement of creating a pivot with x-axis as months - MMMyy and y-axis by top 5 Categories. Currently, the Pivot View is only supports aggregation by Text Category, but if I convert the MMMyy from Date/Time to Text Category, then it does not automatically sort them properly (chronologically)?

Also I need the same feature which we had in the Bar/Line View restricting only top 5 Text Categories in the Pivot y-axis view?

Regards, Rajesh B

DataManager: Omniscope 2.8 - date format error?

acohen — Thu, 01 Aug 2013 11:35:30 -0400

I have a encountered a date format bug, where when I specify the date format for a formula field in DataManager as "dd-MMM-yyyy" it is coming out as "dd-MMM-yyyy HH:MM:SS" and therefore causing failures later on in my DataManager data flow structure.

Can this be corrected asap?

Formulae: Entering literal Date/Time values in formulae

tjbate — Wed, 14 Aug 2013 14:38:32 -0400

You cannot enter literal Date/Time values directly into a formula as text. Instead, you must use a TEXTTODATE function and enclose the literal value characters, and the Date/Time formatting mask characters specifying the exact Date/Time format the literal value will be compared with inside single or double quotes:

Date/Time: Crossing time zones in version 2.7?

rajeshbalu29 — Wed, 20 Jun 2012 01:38:51 -0400

Hi All,
There is a issue in a bar chart axis as attached 2 snapshots one from a licensed version which displays the months properly but if I send the same IOK file to the user who is having a free Viewer access with build 296, the month in axis starts with one month before. Please find the below attached snapshot and let me know icase of any updates on the same.

Regards, Rajesh B

Aggregation: all dates into one date?

amitbdesai — Thu, 08 Aug 2013 13:02:20 -0400

There are so many dates in table, but i want to convert 5 different dates in to one.
For e.g: there are 10 times this date 8/1/2013 with different value in revenue.
But i want to aggregate all this into one with aggregation of sum of revenue and only one time date i.e 8/1/2013.
How can i do this?

Connectors: DoubleClick-Text to date conversion with time zones?

ALC — Mon, 18 Feb 2013 10:49:13 -0500

Hi - I was wondering if anyone else has had this issue - that the date feed from DoubleClick appears as a Text field instead of a Date/Time field and how I can change it so Omniscope recognises it as a Date/Time field?

Thanks, Anna

Date/Time: Display/filter on symbols for Quarters?

hgross — Wed, 05 Sep 2012 21:08:52 -0400

I often duplicate the date field (coming in daily) to create week and month fields by changing the date format output to "ww,yy" or "MMM." It would be great if there was a date symbol for quarter that would easily allow for quarterly rollups. This function is already available in the aggregate menu under "Smart Date Labelling," but would be nice as an option for all date functions added here: http://www.visokio.com/dates-and-times

Date/Time: Reformatting dates imported as integer numbers?

grahamb — Wed, 13 Mar 2013 12:53:00 -0400

Hi. I have a date field which converts correctly from Excel and can be seen in the attached picture. I then wanted to create another field to store the month. The formulae looks correct but the result is the same for every record rather than changing. Again see picture. Any ideas? Also how would I get it to display the month name rather than a number, once the issue is corrected?

Date/Time: Sorting Category filters by Date?

PaulChapman — Wed, 06 Mar 2013 09:01:35 -0500

Hi there, I have a set of data which is date orientated. When I make the date a category in the filter, I see the list of dates in correct date order 02-13, 03-13 etc and I can select the dates which i do / do not want to show. The problem I have is when I change the display from 02-13 to Feb-13. The filter then works alphabetically.

I have not found a way of re-sorting the filter to let me choose the order I want. This is a problem when I want to use pane's side by side, with the oldest date at the top rather than the alpha date at the top. Can anyone suggest a way of doing this?

Thanks, Paul

Dates/Times: organising weekly data by Mondays?

peraltar — Thu, 07 Feb 2013 11:00:43 -0500

Hi - I want to have Omniscope organize the data by each monday of every week so that all days after the monday are calculated in their corresponding measures. My reports need to be organized in this way for weekly summaries. Any idea how to do this?

Thanks, Peralta

Date/Time: Converting Numeric to Date?

Mees — Thu, 24 Jan 2013 07:46:59 -0500

Hi there - I am trying to convert a numeric value into a date value. The numeric is for example 201244, which means the year 2012 and week 44. I am using the Field Organiser in DataManager, and changing the field data type from Numeric to Date/Time and specifying both the the input and output date format both to "yyyyww". The thousand seperator is removed.

The result is that some values are set to null and I can't figure out why this is? Just to be sure, is this the correct way to do this or should I use something else? We often use this type of editing and it normally works just fine.
Thanks

Date/Time: Week 1 2013 in Week Format?

EnyaVDW — Thu, 10 Jan 2013 06:16:48 -0500

Hi,

I am having a problem with changing week one of 2013 from text field into a date/time field.

As you can see in this first screenshot, I already have my year-week data as a text field.

I would like to change this into a date/time field because I'm doing a weekly update for a client since 2011 and the 2011 and 2012 data/time data contains a date/time field and no text field. All data has to have the same type of field of course. So there is no option to leave this data in text field for 2013... When I change my year-week field into date/time, it changes all data except the first week of 2013, it leaves those cells blank... (See image below)

I do not have version 2.8 yet because it is still in its testing phase. Neather can I use the function you have mentionned in another discussion because that will leave my column in a text field.

Do you have any idea how to solve this problem?
Or should I install the 2.8 version to be sure that it works? I am just a little bit scared that it will change my data of 2011-2012 because of some new options.

Thanks in advance,

Enya