Cognos Analytics

 View Only

  • 1.  Extracting month from a field with year-month-day-time

    Posted Tue February 05, 2019 01:07 AM
    Hi! 

    Does anyone know how to extract the month from a member containing date in a "year-month-day-time" format? I'd like to extract just the month into its own column.

    The data source I'm using has these all grouped into one and therefore, I am not not able to group the results by month, thus, resulting in pointless visualisations.

    Thanks!

    ------------------------------
    Thomas Kinnaird
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Extracting month from a field with year-month-day-time

    Posted Tue February 05, 2019 10:29 AM
    @Thomas Kinnaird
    Thanks for posting to the community!

    Which release are you running? 

    Thanks,​

    ------------------------------
    Sami El Cheikh
    Offering Manager
    IBM Cognos Analytics
    ------------------------------

    Original Message


  • 3.  RE: Extracting month from a field with year-month-day-time

    Posted Tue February 12, 2019 04:30 AM
    Hi Sami, 

    Thanks for asking, I'm running 11.0.13. 

    Any suggestions are appreciated!

    Thomas

    ------------------------------
    Thomas Kinnaird
    ------------------------------

    Original Message


  • 4.  RE: Extracting month from a field with year-month-day-time

    Posted Wed February 13, 2019 11:45 AM
    did you try substring function to extract only month?

    ------------------------------
    Raj C
    ------------------------------

    Original Message


  • 5.  RE: Extracting month from a field with year-month-day-time

    Posted Fri February 15, 2019 05:25 AM
    Hi Raj, 

    Thanks for your response. Excuse my ignorance but what do you mean by this, could you attach a snippet to show an example of this? Thanks!

    ------------------------------
    Thomas Kinnaird
    ------------------------------

    Original Message


  • 6.  RE: Extracting month from a field with year-month-day-time

    Posted Wed February 20, 2019 11:52 AM
      |   view attached
    Hi,
    attached document explains how a month extracted from a date time column using cognos function "_month".

    ------------------------------
    Raj C
    ------------------------------

    Attachment(s)

    docx
    Extract Month.docx   83 KB 1 version
    Original Message


  • 7.  RE: Extracting month from a field with year-month-day-time

    Posted Thu February 21, 2019 03:21 AM
    Hi,

    Use expression as EXTRACT(MONTH,dataitem). if month is display as numeric values like 1.. 12 then 
    write expression as 

    case EXTRACT(MONTH,dataitem)
    when 1 then 'Jan'
    when 2 the 'Feb'
    .
    .
    .
    when 12 then 'Dec'
    end

    ------------------------------
    Siva Bonu
    +919560388664
    ------------------------------

    Original Message


  • 8.  RE: Extracting month from a field with year-month-day-time

    Posted Thu February 21, 2019 10:14 AM
    if the source column is not a data time column (eg: 20190221 10:11:25 AM) you may write substring to extract the month.

    substring(20190221 10:11:25 AM,5,2)
    result: 02.

    ------------------------------
    Raj C
    ------------------------------

    Original Message