Visokio website     Downloads     Video tutorials     KnowledgeBase  
Date/Time: How to display day of week? - Visokio Forums
Date/Time: How to display day of week?
  • cristian December 19, 2014 10:38AM
    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?

  • cristian December 19, 2014 11:26AM
    Hmm solved it. Writing my solution if another newbie like me searches for the same info.

    Change format on Date field to text with appropiate block. Split the date into fields with Expand values block.

  •     paola December 19, 2014 12:10PM
    In the DataManager space, Field Organiser block, you can duplicate the [Date] field, in order to preserve the full date, then create [Month] and [Day] fields by preserving the "MMM" and "dd" part. In the next step, either Field organiser, or Data/Manage fields, you can change the data type for the new fields to Category. This will allow you to have tick box filter on the sidebar, also to split by [Day] and layer by [Month] in the Bar/line view, which should give you comparison on the same scale between 1st Jan/1st Feb/1st March and so on.
    Similar can be achieved in the Graph view, if you have [Day] on X axis, [Value] on Y, and connect markers by [Month].

    TimeSplits.JPG 167K
  •     tjbate December 19, 2014 12:13PM
    Cristian - Omniscope has very powerful date/time formatting and arithmetic. Often, in the same file it is worth duplicating a Date/Time Field and changing the data typing of the duplicate(s) to expand the number of filtering and visualisation options. The DataManager Field Organiser makes it easy to duplicate a Date/Time field and to use several different display/storage formats which visualise and filter/sort differently.

    Omniscope is Java, and unlike Excel, stores Dates/Times as a single field. Formatting options for Date/Time display are also based on Java. For example, the EEEE format mask causes the Date/Time to be displayed as the Day of the Week. More formatting options are here:

    There are many functions for transforming values in fields variously typed as Date/Time or Text/Category. Some powerful date manipulation functions (extracted from the full Functions Guide) are listed below:

    DATE(year, month, day, hour, minute, second, millisecond, timezone)

    In scripts:
    date(year, month, day, hour, minute, second, millisecond, timezone)

    Creates a date using numerical arguments. If omitted or null, the first value for each arguments is used. So, if year is null, year 1 is used. If month is null or omitted, January is used, etc. BC dates are not supported. Illegal dates such as day 29 Feb on a non-leap year will be rolled over. Time zone is a text value in the format "GMT-8:00" or "PST"; if omitted, the local time zone is used.

    DATEADD(date, number, unit)

    Adds a specified number of a specified unit to a date.
    To subtract, use a negative number.
    Unit is optional; if not specified, "day" is assumed, otherwise it should be specified as:
    "year", "month", "week", "day", "hour", "minute", "second", "millisecond".

    DATEDIFF(date1, date2, unit, approximate)

    Establishes the difference between two dates, as a decimal number of a specified unit (optional, default: "day").
    If date2 is before date1, the result will be negative. e.g. the decimal number of weeks between two dates.
    Units should be specified as: "year", "month", "week", "day", "hour", "minute", "second", "millisecond".

    You can optionally choose approximate date difference for faster value calculation by specifying "true" for "approximate". Approximate date difference divides elapsed time between the two dates by the typical unit length (e.g. 30 days for days-in-month).

    DATETOTEXT(date_value, custom_format, time_zone)

    Converts a Date into text, optionally using a custom date format and time zone.

    DATEUNIT(date, unit)

    Retrieves a specified unit from a date as a numeric value. For example, the date "12 Feb 2006" has 12 as the "day" unit. Unit is optional; if not specified, "day" is assumed, otherwise it should be specified as:
    "year", "month", "week", "day", "hour", "minute", "second", "millisecond".
    NB. "days" is interpreted as "day of month", "weeks" as "week of year", and "hours" as "hour of day (24)".

    DATEVALUE(text) Converts a text string that represents a date to a date. For more options, see TEXTTODATE.

    TEXTTODATE(text_value, custom_format, lenient, time_zone)

    Converts text into a date. If custom_format is specified, this is a custom date format pattern such as "dd/MM/yyyy HH:mm:ss". If not specified, the default format for the current data locale will be used.

    If lenient is omitted or is true, this controls whether non-existent dates are permitted, such as 29 February on a non-leap-year (corrected to 1 March).

    If time zone value is specified, this controls how Omniscope interprets time values. If not specified, the text will be assumed to be a date/time from the same time zone as the system.

  • cristian December 22, 2014 3:59AM
    Thanks. I will test some functions. I probably will ask more questions later :D
  •     tjbate December 22, 2014 9:59AM


It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership