Cognos Analytics

 View Only
Expand all | Collapse all

Incorrect Totals Displayed When Using the Total Function with Groups

  • 1.  Incorrect Totals Displayed When Using the Total Function with Groups

    Posted Tue September 03, 2024 01:50 PM

    I am having an issue with the total function when used on a query calculation when groups are used and ran as an excel. 

    The data item I am trying to total is defined as:

    if ([Type Code] in (0001, 0002))
    then
    ([Transaction Amount] * -1)
    else
    ([Transaction Amount])

    I changed all the summary aggregations to calculated. If all the groups are removed from the report and I only apply a total function to the calculated field, the negative numbers look correct in Excel. But when the report is ran with the two original groupings and sub-totals, the negative numbers are incorrect in Excel. 

    Has anyone else had this issue?

    Is this a feature that does not carry over to Excel?

    Thanks, 



    ------------------------------
    Elizabeth Johnson
    ------------------------------


  • 2.  RE: Incorrect Totals Displayed When Using the Total Function with Groups

    IBM Champion
    Posted Wed September 04, 2024 04:58 AM

    Hi Elizabeth, 

    I can't follow each of your steps precisely. But if you are using this data item in summaries then the summary aggregation total instead of calculated seems correct to me and no further total function would be required. 

    Best regards

    Philipp



    ------------------------------
    Philipp Hornung
    Business Intelligence Manager
    Techniker Krankenkasse
    Hamburg Germany
    #IBMChampion
    ------------------------------



  • 3.  RE: Incorrect Totals Displayed When Using the Total Function with Groups

    Posted Wed September 04, 2024 08:58 AM

    I can see how the original message could be confusing. 

    I first created the report with the data item. 

    Then, I added the groups to the report. 

    After that, I did a summarize total on the data item. 

    Once created, I changed the summary aggregation to calculated. 



    ------------------------------
    Elizabeth Johnson
    ------------------------------