Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 

 View Only

  • 1.  Calculations to convert 'Yes' to '1' and 'No' to '0'

    Posted Wed August 31, 2022 10:14 AM
    I have some columns with Yes/No data and am trying to convert them to 1/0 so I can preform calculations and counts. What formula in calculations will help me perform this swap?

    I don't know what calculation formula to use, but I found this one online:

    CASE
    WHEN [DATASET] in ('Y') THEN '1'
    ELSE '0'
    END

    I get the error message: The expression is not valid. I also tried sorting the DATASET by group, to group Yes to 1 and group No to 0, but then that messed up the visualizations in my dashboard.

    What is the best way to go about this?


    ------------------------------
    Kristen Hafford
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Calculations to convert 'Yes' to '1' and 'No' to '0'

    Posted Wed August 31, 2022 11:44 AM
    Hi Kristen,

    Here is a working example:

    case [Country]
    when 'Canada' then (1)
    when 'United States' then (1)
    else (0)
    end

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------

    Original Message


  • 3.  RE: Calculations to convert 'Yes' to '1' and 'No' to '0'

    Posted Wed August 31, 2022 11:47 AM
    Kristen,

    Another idea, you might want to check for Y, N and maybe Unknown answer (or whatever). So the logical expression would be:
    case [Answer]
    when 'Y' then (1)
    when 'N' then (0)
    else (null)
    end

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------

    Original Message


  • 4.  RE: Calculations to convert 'Yes' to '1' and 'No' to '0'

    Posted Wed August 31, 2022 03:44 PM

    That syntax should be OK.  Patrick's suggestions should be OK to and, in the case of the latter, have the advance of handling all possible states.

    Usually there *should* be more text to the error.  Was there anything else in the error message?



    ------------------------------
    IAN HENDERSON
    ------------------------------

    Original Message


  • 5.  RE: Calculations to convert 'Yes' to '1' and 'No' to '0'

    Posted Wed September 14, 2022 04:19 AM

    Your '1' and '0' will be treated as strings/characters. If you want to use these as measures, for example total up the number of Yes, then you want the number 1, not the string 1. Remove the quotes for a number.

    You can total up numbers, for example 1+1+0+1, but you cant total up strings, for example '1'+'cat'+'dog'+'0'.

    It may simply be that you were trying to use a string in a chart.



    ------------------------------
    Marc Reed
    ------------------------------

    Original Message