Calculation Help - Need To Exclude Certain Rows From Sum

[pnn44794]

Hello:

I need to exclude certain rows from the sum of the rest of the rows.  I'm guessing that I'm missing something simple, but maybe not.  My original expression, which is including the rows I don't want, is:

Num(Sum({<[Approval Completed Quarter] = {"2018-Q2"}, CCB_PROD_KEY3 -= {"VSI-Power (AIX)"}, CHANGE_TYPE *= {"Install"}>} NET_ASSETS), '#,##0')

I need to exclude rows where the Service Ordered value is like '*DBaaS*'.  The full Service Ordered values are 'Database as a Service (DBaaS)' and 'DBaaS ICP2', that I want to exclude.  I've tried modifying my original expression above to the following, but those rows are still included in the Sum total:

Num(Sum({<[Approval Completed Quarter] = {"2018-Q2"}, [Service Ordered] -= {"*DBaaS*"}, CCB_PROD_KEY3 -= {"VSI-Power (AIX)"}, CHANGE_TYPE *= {"Install"}>} NET_ASSETS), '#,##0')

As always, any and all help and responses is appreciated.  Thanks in advance.

Message was edited by: Perry Newman - Corrected column name.

[pnn44794]

So, are you saying create the Flag in my load script and then use that in my expression?  How would that then exclude the rows that have *DBaaS* in the Service Ordered column, especially if CCB_PROD_KEY3 is null / blank?  Sorry, but I simply don't understand how it would work.

For the second option, I believe I get it.  Put that in my load script and then my existing expression would probably work because CCB_PROD_KEY3 would no longer be null.  Do I understand that correctly?

[Anonymous]

yes,

* stands for intersection... I can't see a mistake in a quick way, please post a scrambled sample

[sunny_talwar]

Sorry, I might have misunderstood the issue... but isn't the problem that CCB_PROD_KEY3 is null for the rows that you want to count?

[Anonymous]

ok, too many brackets

Num(Sum(

{

(< [Approval Completed Quarter] = {"2018-Q2"},  CHANGE_TYPE *= {"Install"}>)

*(1-<CCB_PROD_KEY3 = {"VSI-Power (AIX)"}>)

*(1-<[Service Ordered] = {"*DBaaS*"}>)

}

NET_ASSETS), '#,##0')

[Anonymous]

I created a small example for you:

A shall be 1, B shall be 1 and C may not be 5 and D may not be 5

[pnn44794]

No.  I need to exclude rows where Service Ordered is like '*DBaas*'.  There are 27 rows where DBaaS is present.  My current expression excludes 2 of the 27 and I noticed that the 25 that are not excluded do not have a value for CCB_PROD_KEY3.  Make sense?

[sunny_talwar]

That is weird... I am not sure why this would happen... do you think you can share your data in an Excel file?

[pnn44794]

Is this meant for my post or a different post?

[pnn44794]

No problem.  I appreciate you responding and thanks for making the correction.

I tried the expression and I get the same results.

[Anonymous]

yes, this is a basic demo how to exclude values by set analysis but include null() values.

I also have posted a corrected expression above.