Tagged with date_time_formulae - Visokio Forums

Comparing Date values: Filtering dates after 2014

Phillipa — Wed, 03 Aug 2016 05:54:58 -0400

Hi all,

I am totally new to dates in Omniscope and would like to know how to select dates post 2014.

I have a field in date format dd.MM.yyyy HH:mm:ss and want to select records that are only after 2014.

I know it is not rocket science but as a beginner I need some help.

Thanks.
Phillipa

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: Mobile Date Ranges in Different Time Zones?

OliverB — Tue, 19 May 2015 05:15:41 -0400

Hi,

We are having an issue when our clients access mobile reports from different time zones. With a simple data filter, the displayed date jumps back a day whenever a specific date is typed in. We also have some reports with more complex date variables and these don't work correctly either when opened in a different time zone.

I have been able to replicate this on my machine by changing the windows time zone. It is hard to show this from a screenshot, but am happy to jump on a screenshare to demonstrate.

The files have all been published using a 2.9 (2.9 b1641) scheduler on GMT time zone, and the mobile server is also running 2.9 (2.9 b1613) and is on GMT. The time zones I have tested this on are South African Standard time and US Eastern Time.

SUBSET Functions: Calculating sum within a date range

DDrob — Fri, 01 May 2015 11:26:48 -0400

Hi guys

I haven't posted here before, I usually manage to work my issues out if I look at it for long enough but this one is just puzzling me. I feel like I'm doing everything right but I'm not getting the desired outcome.

My data is sales data, split by day and email address, where each row is a sale. I'm trying to create a new field which determines whether the sale was from a new customer (this was their first purchase in the last year), or an existing customer (purchased in the last year).

My steps were as follows:
1. Create a new field (StartDate) which is a DATEADD of -1 year to determine the lower end of my existing customer bracket.
2. Create a new field which is a function of SUBSET_SUM, SUBSET3 where Email=Email,Date>StartDate,Date
This will give me a sum of the sales where the email has appeared previously within my date range, and I can take 0 values as new and anything else as existing.

Now, my new field isn't returning anything and I can't work out why.

Hope you can help.

Thanks

P.S. I will attach the formula I used when I get access to my file again, although I'm pretty sure my syntax is correct.

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: Calculating Week Start - Week Ending dates?

Panos — Wed, 17 Sep 2014 13:48:34 -0400

Hi
Is there any formula so I can get the "week start" or "week end" for each week number?
Regards,
Panos

Formulae: Subset Functions-Record count within lookback window/ Records between

alexandervolk — Mon, 11 Aug 2014 05:31:16 -0400

I have a set of timestamped data (page visits) in which - for each record - I want to calculate the number of page visits within the last N minutes. I have a device which sets N as a parameter, defaulted to 5 minutes.

In a second field next to "timestamp", I am calculating "timestamp-N" for the lookback window. My initial idea was to use the RECORDCOUNT formula and for each record, calculate the record count between MIN[timestamp-N] and MAX[timestamp]. That doesn't seem to work ...or at least I can't get the formula right.

Are there other ways to get the results I need?

Thanks for your help!
Alex

Date/Time: Detecting Year as Date/Time w/o thousand separators?

chrisamott — Fri, 23 Apr 2010 14:24:48 -0400

When using the DATEUNIT function to determine the year of a date, by default the result is displayed as an Integer number with thousand separators. Using Data > Manage Fields, we can reformat to remove the separators if we needed a plain integer displayed.

However, when using DATEUNIT as part of a larger function, to create a string YEAR + "Quarter 2" for example, the thousand separator cannot be so removed and the result, '2,009 Quarter 2' is far less preferable than '2009 Quarter 2'.

Can this be resolved?

Formulae: Adding Time to Dates?

Valentina — Wed, 18 Jun 2014 03:49:57 -0400

Hi,

I have a Date column and a column showing the number of days ahead. Eventually I would like to get the Date ahead i.e. if the Date today is 10/06/2014 then in two days it will be 12/06/2014. Please see the example below.

[Date] [ DaystoAdd] [Date ahead]
10/06/2014 2 12/06/2014
04/06/2014 5 09/06/2014
30/05/2014 6 05/06/2014

Any ideas how to achieve this?

Thanks in advance

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

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:

Idea: Pivot View-Date field as measure?

ygulla — Mon, 15 Jul 2013 08:37:36 -0400

When selecting a date field as a measure in pivot view, there is no way to choose a meaningful aggregation (e.g. mean, min, max). It seems strange given that date aggregation works fine everywhere else. Is it possible to add all aggregation functions for date measures in Pivot Views?

I've tested this in 2.8 b1014.

Yuri

Aggregation: Grouping records by date?

nicci113 — Fri, 17 May 2013 06:09:27 -0400

Hi,

Is there a way to group dates within Data Manager? I'm creating a primary key, but need the group function ?

Date/Time: Numeric converted to Date/Time?

Mees — Thu, 31 Jan 2013 09:47:31 -0500

Hi there - I refer to the discussion:

http://forums.visokio.com/discussion/1995/datestimes-converting-numeric-to-dates

I see that thread is closed, but the problem still exist. What I meant was that the correct Java version was used by Omniscope. However, we are still experiencing the following problem:

Only for the first week in 2013, converting numeric value '201301' to a datevalue (format yyyyww) results in null...
for all other weeks it works.

Any ideas?

Formulae: Working days between dates?

enrico68 — Wed, 16 Jan 2013 05:04:00 -0500

Hi - I need to calculate working days between to dates. I have already seen the function WorkingDaysBetweenDates that has been described in previous posts.

I'd like to know how to use this function in a 'html extended' script in a Content View and how to call this function in the same script?

Thanks, Enrico

Transformation: Date range split?

dszl136154 — Mon, 19 Nov 2012 11:25:55 -0500

HI,
I was wondering if Omniscope could solve the following problem?

I have the following table:

Start Date End Date Spend
20-Sep-12 27-Sep-12 £4,200
28-Sep-12 01-Oct-12 £2,000

I would like to draw one bar for each day between start and end dates sized by an average spend per day. I reckon to do this, I would need to convert the data into the following format:

Date Spend
20-Sep-12 £600
21-Sep-12 £600
22-Sep-12 £600
23-Sep-12 £600
24-Sep-12 £600
25-Sep-12 £600
26-Sep-12 £600
27-Sep-12 £600
28-Sep-12 £500
29-Sep-12 £500
30-Sep-12 £500
01-Oct-12 £500

Is it possible to do it in Omniscope? If yes, could you help me with it, please?
Thanks

Formulae: Calculating differences in dates dynamically?

Simon_Maynard — Tue, 08 Jan 2013 07:01:10 -0500

Hi,

I am using a "Record filter" block to filter for data relating to today’s date minus 14 days (Example 08/01/2013 - 25/12/2012). At the moment I am manually changing the date everyday.

Is there a way of using a formula that automatically updates every time I open the file so the output table always only contains the last 14 days.
In Excel I would use: =TODAY()-14

The source of the data is from a database in which I query at the beginning of the process. I was hoping to add a line to the SQL at this stage to only pull in the last 14 days but again I could not find an option to do so.

Please could someone let me know how I can do this?

Date/Time: Adding 10 min to a date/time value?

sara_llamasoft — Fri, 23 Nov 2012 10:57:56 -0500

Hi Folks - I'm probably missing something obvious, but here's what I need to do (working in US dates here):
- I have a date field [RouteDate], e.g. 8/12/2012 (Aug 12)
- I have a time field [StartTime], e.g. 12:45 (assume 24-hour time)
- I want to combine these into a date-time field and add 10 minutes, e.g. 8/12/2012 12:55. I'm trying to create a shipment delivery time window. Of course, I want this to be time-date math, so that if the time were 23:55, my new value would be 8/13/2012 00:05.

I tried many combinations of DATEADD, DATEVALUE, TEXTTODATE but nothing seems to yield the date-time value I'm after.

Any ideas would be most appreciated.

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?

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