topic Re: Calculation Help - Need To Exclude Certain Rows From Sum in QlikView

Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 15:44:35 GMT

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.

Re: Calculation Help - Need To Exclude Certain Rows From Sum

sunny_talwar — Thu, 14 Jun 2018 16:42:36 GMT

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 16:51:24 GMT

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?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

sunny_talwar — Thu, 14 Jun 2018 16:53:18 GMT

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 17:01:53 GMT

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.

Re: Calculation Help - Need To Exclude Certain Rows From Sum

sunny_talwar — Thu, 14 Jun 2018 17:13:23 GMT

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?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 17:15:18 GMT

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

sunny_talwar — Thu, 14 Jun 2018 17:23:49 GMT

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

Anonymous — Thu, 14 Jun 2018 17:28:45 GMT

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')

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 17:49:28 GMT

Hello Robin:

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

Are the * supposed to be there?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 17:54:01 GMT

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?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

Anonymous — Thu, 14 Jun 2018 17:58:09 GMT

yes,

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

sunny_talwar — Thu, 14 Jun 2018 18:03:50 GMT

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?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

Anonymous — Thu, 14 Jun 2018 18:19:49 GMT

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')

Re: Calculation Help - Need To Exclude Certain Rows From Sum

Anonymous — Thu, 14 Jun 2018 18:22:30 GMT

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 18:43:41 GMT

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?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

sunny_talwar — Thu, 14 Jun 2018 18:48:50 GMT

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

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 18:50:09 GMT

Is this meant for my post or a different post?

Re: Calculation Help - Need To Exclude Certain Rows From Sum

pnn44794 — Thu, 14 Jun 2018 18:50:49 GMT

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

I tried the expression and I get the same results.

Re: Calculation Help - Need To Exclude Certain Rows From Sum

Anonymous — Thu, 14 Jun 2018 18:51:44 GMT

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.