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?
  •        hgross March 13, 2013 1:28PM
    Did you try duplicating the date field and making the date format output "MMM"

    Screenshot attached.
    Date to Month.PNG 35K
  •     grahamb March 13, 2013 1:41PM
    Here is sample of excel data for you to try
  •     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

  •        hgross March 13, 2013 1:56PM
    I took the file you provided and manipulated the fields using a field organizer. File attached.
    Date to 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:


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

Sign In Apply for Membership