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.

[sunny_talwar]

Set analysis looks good to me... are you sure it is not getting excluded?

[pnn44794]

Hello Sunny:

Thanks for the response.  Ok.  I'm slightly wrong.  It does exclude 2 rows where there is a value for CCB_PROD_KEY3.  There's 27 rows I'm trying to exclude in total.  So, now I'm down to 25.  The remaining 25 do not have a value for CCB_PROD_KEY3, but do have values for the other columns in the set analysis.  Do I need to account for the missing CCB_PROD_KEY3 values?  If yes, how would I do it?

[sunny_talwar]

What id CCB_PROD_KEY3 and how is it related to [Service Ordered]?

[pnn44794]

CCB_PROD_KEY3 should be one of the following values:

PSI-Basic

PSI-Basic-HDD

PSI-Business

VSI-D

VSI-Non-Prod

VSI-Prod

and be related to one of the following Service Ordered values:

VSI ICP2

Virtual Server Infrastructure (VSI)

Physical Server Infrastructure (PSI)

The above values are the full value names.

[sunny_talwar]

But you are not using CCB_PROD_KEY3 in your expression? How is that impacting anything? Is that a dimension? or CCB_PROD_KEY3 and PROD_KEY3  are related somehow?

[pnn44794]

Sorry.  Typo above.  My fault.  It is CCB_PROD_KEY3.  I'll edit my post to reflect the same.

[sunny_talwar]

That makes sense.... may be create a flag for this field

If(CCB_PROD_KEY3 <> 'VSI-Power (AIX)', 1, 0) as Flag

and now use this Flag in your script....

Alternatively, you can replace nulls with white spaces

If(Len(Trim(CCB_PROD_KEY3)) = 0, ' ', CCB_PROD_KEY3) as CCB_PROD_KEY3

and not continue to use the same expression

[Anonymous]

try something like

Num(Sum(

{

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

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

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

}

NET_ASSETS), '#,##0')

[pnn44794]

Hello Robin:

The expression you posted above gives me the following error:  "Error in set modifier expression"

Are the * supposed to be there?