Visokio website     Downloads     Video tutorials     KnowledgeBase  
Date/Time: Reformatting dates imported as integer numbers? - Visokio Forums
Date/Time: Reformatting dates imported as integer numbers?
  •     grahamb March 13, 2013 12:53PM
    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?
    Attachments
    		 omniscope_date_conversion.png 57K
  • 7 Comments
  •        hgross March 13, 2013 1:28PM
    Did you try duplicating the date field and making the date format output "MMM"

    Screenshot attached.
    Attachments
    		 Date to Month.PNG 35K
  •     grahamb March 13, 2013 1:41PM
    Here is sample of excel data for you to try
    Attachments
    		 omniscope date issue.xlsx 11K
  •     tjbate March 13, 2013 1:51PM
    Graham - This is not a bug, but rather a confusing aspect of duplicating fields in Omniscope Field Organiser blocks. Your external source data is an integer number meant to stand for a date: 20120313. Using a Field Organiser, you told Omniscope to read this integer number in as a Date/Time field using yyyyMMdd and saving/displaying the result as dd-MM-yyyy.

    You then duplicated this field so you could re-type as Text/Category in order to have more filtering and visualisation options. Unfortunately, the duplicate field is still referencing the original field's source data integer number.

    To work around this, you need to use a formula field that will read NOT the original source integer number, but the field which has already been transformed into a proper date, then output that parsed date as a text string suitable for categorisation:

    TEXTVALUE([Dates as Dates],'yyyy-MM-dd')

    See example file attached



    Attachments
    		 Re-typing Integers as Dates and Text.iok 7K
  •        hgross March 13, 2013 1:56PM
    I took the file you provided and manipulated the fields using a field organizer. File attached.
    Attachments
    		 Date to Month.zip 18K
  •     grahamb March 13, 2013 2:08PM
    I see. Thanks. I had yyyymmdd which does not work. Changing to yyyyMMdd does work. I did not realise the formats are case sensitive. Should they be?
  •     grahamb March 13, 2013 2:19PM
    BTW - can you do parato charts anywhere in the system?
  •     tjbate March 14, 2013 7:43AM
    Graham - Pareto is coming, please add your votes and specifications/use cases here:

    http://forums.visokio.com/discussion/665/idea-pareto-custom-rangess
Add a Comment

Welcome!

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

Sign In Apply for Membership

Categories

Tagged