Issue in finding sum of rows inside a column

[asmfloyd]

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,

[dougp]

Code Select Expand

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 Expand


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 Expand


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.

[asmfloyd]

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