Cognos Analytics

Cognos Analytics

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

 View Only

  • 1.  Calculate percentage between 2 values ​​in the same column, per month

    Posted Mon August 28, 2023 04:42 PM

    Hey guys,

    A question: I have a query that results in the following table:

    DATABASE CLIENT TYPE  QTD CLIENT 
    2023-03 consumer                    3.589
    2023-03 total             5.746.832
    2023-04 total             5.785.543
    2023-04 consumer                    3.639
    2023-05 total             5.822.574
    2023-05 consumer                    4.294
    2023-06 consumer                    4.417
    2023-06 total             4.936.128
    2023-07 consumer                    5.244
    2023-07 total             5.880.184

    How to add a calculated column with "% of consumer over total per month"?

    DATABASE CLIENT TYPE  QTD CLIENT  % OF CONSUMER OVER TOTAL expected value
    2023-03 consumer                    3.589   (like 3.589 / 5.746.832) 0,06%
    2023-03 total             5.746.832 0,06%
    2023-04 total             5.785.543 0,06%
    2023-04 consumer                    3.639 0,06%
    2023-05 total             5.822.574 0,07%
    2023-05 consumer                    4.294 0,07%
    2023-06 consumer                    4.417 0,09%
    2023-06 total             4.936.128 0,09%
    2023-07 consumer                    5.244 0,09%
    2023-07 total             5.880.184 0,09%

    #Cognos Analytics with Watson



    ------------------------------
    Gustavo Andrade
    Data Analyst
    ------------------------------


  • 2.  RE: Calculate percentage between 2 values ​​in the same column, per month

    Posted Tue August 29, 2023 03:56 AM

    Hi Gustavo,

    Here is one solution for your issue.

    The first query is:

    With the following data:

    Create a Query2 with the following:

    Create a Query3:

    The Join relationship is the following:

    In Query3, you will have the following data items:

    Data Item1 is a calculation with the following expression:

    if ([Query1].[Client] = 'C') then ([Query1].[Quantity]/[Query2].[Quantity2]) else (1)

    Create a list based on Query3:

    Best regards,



    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    IBM Champion
    ------------------------------

    Original Message


  • 3.  RE: Calculate percentage between 2 values ​​in the same column, per month
    Best Answer

    Posted Wed August 30, 2023 01:54 AM

    Hi,

    Patricks solution is one way. If you don't want the multi-query report you could do it like this.

    The Consumer data item only includes the consumer data like this:

    The Total data item only includes the Total data like this:

    Then the third data item called Percentage is given the expression:

    Best Regards,



    ------------------------------
    David Kristensson
    ------------------------------

    Original Message


  • 4.  RE: Calculate percentage between 2 values ​​in the same column, per month

    Posted Wed August 30, 2023 02:31 PM
    Edited by Gustavo Andrade Wed August 30, 2023 02:37 PM

    Many thanks @Patrick Neveu and @David Kristensson

    It worked ! 
    I used the second solution, as my use case was just an indicator that this percentage was missing.



    ------------------------------
    Gustavo Andrade
    Data Analyst
    ------------------------------

    Original Message