Cognos Analytics

Expand all | Collapse all

Dates are duplicated in Data Module by swapping Day and Month

  • 1.  Dates are duplicated in Data Module by swapping Day and Month

    Posted Sat March 14, 2020 03:33 PM
    I uploaded a very simple text file with two columns, one for the date (in dd/MM/yyyy format) and a second one for the measure.

    During the analysis of this file in Cognos Analytics I noticed values for dates that were not in the uploaded text file or dates with duplicated values. And the reason is that the Data Module seems to be swapping days and months for some rows.

    As you can see in the screenshots, the sixth row is changed in the data module from first of February to second of January

    What do you think? Is this a bug?


    ------------------------------
    Alex Santamarta
    ------------------------------


  • 2.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Mon March 16, 2020 07:48 AM
    Hello Alex

    I have been unable to reproduce the error.

    I have tried three types of data, txt, xlsx, xls and csv and in all the data appears correct. 

    CSV

    TXT


    XLS


    XLSX



    What I have seen is that the behavior in the format of the dates is different by language and by type of origin. As you can see in the images the data format is different in CSV and TXT than in XLS and XLSX

    What version do you use? What language settings does the Cognos user have?

    I hope I have helped you




    ------------------------------
    Luis Moreno
    ------------------------------



  • 3.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Mon March 16, 2020 09:27 AM
    Hi Alex,
    Which version of Cognos Analytics are you using?
    Could you attach the file as well please?
    // Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 4.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Mon March 16, 2020 02:01 PM
      |   view attached
    Hello,

    here the additional information requested:

    Cognos Version:
    IBM Cognos Analytics 11.1 R5 (Analytics Cloud)

    Language Configuration:
    Product Language -> English
    Content Language -> English

    Additionally this is how I formatted the date column in the Data Module:

    The text file used as source is attached to the thread (text.txt)

    Regards




    ------------------------------
    Alex Santamarta
    ------------------------------

    Attachment(s)

    txt
    test.txt   129B 1 version


  • 5.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Mon March 16, 2020 02:35 PM
    Is it possible to change date format to mm/dd/yyyy in excel to see if that corrects the issue in Data Module?

    ------------------------------
    Buddhi Gupta
    ------------------------------



  • 6.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Mon March 16, 2020 02:46 PM
    Edited by Alex Santamarta Mon March 16, 2020 02:50 PM
    Hello Buddhi,

    The problem is that the source of the data are not Excel files, but text files that are using this date format dd/MM/yyyy.

    But I think you are right. It seems the data module is expecting that date columns use the MM/dd/yyyy format or yyyy-MM-dd format, not dd/MM/yyyy in any case.

    The program that generates the text files would have to be changed to use any of the two valid formats

    I naively thought that the format column option in the data module was to specify how the date parts were layed out in the date column of the text file.

    ------------------------------
    Alex Santamarta
    ------------------------------



  • 7.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Mon March 16, 2020 04:03 PM
    Hi Alex,
    I was able to reproduce the issue that you reported.
    Confirming your observation:  the format that you specify in the Data Module is not how it treats the input, but it specifies how it is rendered.
    I would consider this a bug. It seems to treat favour the mm/dd/yyyy parsing, but when it fails it uses dd/mm/yyyy, causing an inconsistent view of the data.
    Are you able to log a bug for this?
    // Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 8.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Tue March 17, 2020 09:43 AM
    Hello Henk,

    I don't think that I can log a bug because I'm still in the trial period.

    ------------------------------
    Alex Santamarta
    ------------------------------



  • 9.  RE: Dates are duplicated in Data Module by swapping Day and Month

    Posted Wed March 18, 2020 03:16 PM

    I used to see this issue many times when I worked with the Cognos tools that were Windows-based, and this may be the same issue here.
    How is the Windows local variables for date set?
    If they are set to "Europe" where dates are displayed as DD/MM/YYYY, that is what the data is expected to be, no matter what you entered.
    You'll need to check your local system and the Cognos server(s) as well.
    here's another way to test: Create a record for 12/31/2019​ and see if that gets processed. I suspect you'll get an error that 31 is an invalid month number.
    Good luck,

    Opher



    ------------------------------
    Regards,
    Opher
    ------------------------------