Print Page - Issue in finding sum of rows inside a column

Title: Issue in finding sum of rows inside a column
Post by: asmfloyd on 19 Feb 2023 08:23:02 PM

Hi,
I have a table which has the list of funds, the shares in each fund along with rating of each shares (A,B,C) for years 2022 and 2023.
I want to list the fund names, total shares for each fund in 2019,shares of each fund with category as "A" in 2019.

So I created a variable column v2 to find sum of shares of fund with category A:
If (year=2019 and category='A') THEN (1) ELSE (0). Then marked detail aggregation as "Total". It gives me the sum of shares for category A for Fund1. Working fine.

Now I created another variable column v1 to get total shares in a fund:
If (year=2019) THEN (1) ELSE (0). Then marked detail aggregation as "Total". It populates as just "1" for all fund rows. Not total'ing the values

Is there anything I am doing incorrect? OR why the total is not getting populated?

Thanks,

Title: Re: Issue in finding sum of rows inside a column
Post by: dougp on 21 Feb 2023 11:41:09 AM

Code Select

If (year=2019 and category='A') THEN (1) ELSE (0)

...doesn't seem like it would give you the sum of shares. It gives you the number of rows.

Sample inputs:

Code Select


Fund   Category    Year    Shares
Fund1  A           2019     17
Fund1  A           2020     25
Fund2  B           2019     44
Fund3  A           2019     50

I think your code would give you:

Code Select


Category  Year   Shares
A         2019   2
A         2020   1
B         2019   1

But I think you're overthinking this. Why is this not a list with groupings or a crosstab?

Maybe sample inputs and expected outputs would help.

Title: Re: Issue in finding sum of rows inside a column
Post by: asmfloyd on 21 Feb 2023 03:10:36 PM

Hello dougp,
Thank you very much for your reply. I am sorry for the confusion. Attached is my scenario with expected output.

Now below is my logic. I now think whether this is occurring because year is just a dimension without a fact? If I change the logic of v1 as (year=2019 and position is not null) then it works.

Variable2 If (year=2019 and position=1) THEN (1) ELSE (0). Then marked detail aggregation as "Total". It gives me the sum of shares for position 1 for Fund1/2/.... Working fine.

Now I created another variable column v1 to get total shares in a fund:
If (year=2019) THEN (1) ELSE (0). Then marked detail aggregation as "Total". It populates as just "1" for all fund rows. Not total'ing the values

Thanks,

Data      FUND   YEAR   SHARE   POSITION
      Fund1   2019   A   1
      Fund1   2019   B   2
      Fund2   2019   C   3
      Fund3   2019   D   1
      Fund3   2019   F   1

      Fund3   2020   D   1
      Fund2   2020   C   2



Result expected
      FUND   2019Total Shares for each fund    2019No of Shares with posn 1 for each fund

      Fund1   2   1
      Fund2   1   0
      Fund3   2   2

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: asmfloyd on 19 Feb 2023 08:23:02 PM