Aggregation in the Calculation view is not working

[Former Member]

Hi Everyone,

I have a calculation view in which I need to create a calculated column "Count all the account names with amount greater than 1million and type is business". If the account names are repeating it has to sum all the amounts for that account

ACCOUNTNAME	AMOUNT
A	500000
A	600000

and check if it is greater than 1million(A(1100000)>1million). We cannot put any condition in Filters as it is specific to this calulated column. I tried to create a model below but the aggregation is not working for the repeating accounts.

In the above view in the

Projection 1    : I created a calculated column "CC_TYPE" : if ( TYPE='BUSINESS',"ACCOUNTNAME",'0')

Aggregation_1 : Another calculated column "CC_FINAL"    : if( CC_TYPE !='0' and AMOUNT>1000000,1,0). Here we have Columns as Account name and Amount.

Projection_2   : Columns Accountname,CC_FINAL

Aggreagtion   : Columns Accountname,CC_FINAL

Semantics     : Accountname,SUM(CC_FINAL)

But for me aggregation is not happening in the second layer (AGGREGATION_1). A is above 1million but it checks each row and both are below 1million it neglects that account and make count as 0. I am not sure where is the mistake.Please suggest or correct me if it is wrong.

Thanks in advance.

[sreelatha_reddy2]

Hi Mani,

I have created a sample table as per your requirement.  I have added filters in the project layer for Type(=Business) and Amount (>=100000) and my data preview is giving me the desired results. Let me know if I am missing anything here.

CREATE COLUMN TABLE SREELATR.AGGR

(TYPE CHAR(13),

ACCOUNT CHAR(2),

AMOUNT INTEGER);

INSERT INTO SREELATR.AGGR  VALUES ('BUSINESS','A',90000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','A',100000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','A',500000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','A',700000);

INSERT INTO SREELATR.AGGR VALUES ('AGRI','B',6000000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','B',700000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','B',500000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','B',100000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','B',200000);

INSERT INTO SREELATR.AGGR VALUES ('BUSINESS','A',-400000);