Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  case statement calculation in dashboard

    Posted Thu January 09, 2020 09:01 AM
    Hi All,

    I am trying to write a case statement in a Cognos 11.1.4 dashboard to limit a measure.
    example

    case when [year] = 2018 then [revenue] end

    I am using this case statement as a filter to create a new measure that I can use in further calculations.

    I am getting 0 as a result. However if I put year in the filters and revenue on the canvas and filter for 2918 I am getting a valid number.

    Any suggestions?

    ------------------------------
    BILL DONOVAN
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: case statement calculation in dashboard

    Posted Thu January 09, 2020 12:19 PM
    A syntax like the following works for me

    IF ( YEAR = 2018 ) THEN ( REVENUE ) ELSE ( 0 )

    ------------------------------
    JASON TAVOULARIS
    ------------------------------

    Original Message


  • 3.  RE: case statement calculation in dashboard

    Posted Thu January 09, 2020 01:03 PM
    Hi Jason, I find this only works after three things are done

    1) make sure the calculation is done before aggreation
    2) set properties of the calculation field to measure
    3) set the aggregation method to none

    After that is done the If statement appears to work.

    ------------------------------
    BILL DONOVAN
    ------------------------------

    Original Message


  • 4.  RE: case statement calculation in dashboard

    Posted Fri January 10, 2020 09:19 AM
    ​Hey Bill:

    I'm very new to Cognos dashboards and Cognos in general but I'm trying to do something very similar.  In your number 1 above, when you say "make sure the calculation is done before aggregation, how does one do that?

    Thanks for the help!

    Scott Ford

    ------------------------------
    Scott Ford
    ------------------------------

    Original Message


  • 5.  RE: case statement calculation in dashboard

    Posted Mon January 13, 2020 04:31 PM
    I found this link to be helpful in learning how to do that (useful even for your use-case which is not percentages): Considerations for percentage calculations in dashboards in Cognos Analytics 11

    ------------------------------
    Victoria Ratkos
    ------------------------------

    Original Message


Related Content