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]

Sure thing Sunny.  It'll be a little later before I get to it.  Hopefully in an hour or so.  I'll provide the total I believe I should be getting too.

[pnn44794]

Hello Sunny:

Are you seeing anything out of the ordinary?  I ask, because I'm now seeing 919 for a total sum.  The data attached in my previous post is from late yesterday and would not reflect this new number.

After closing the dashboard normally, closing the Qlikview desktop client and then restarting it, the total does drop to 919.  919 could be legit as there's been a couple of days between data refreshes.

[sunny_talwar]

Checking

[sunny_talwar]

When I reloaded the data and used this expression

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

I got 940. Not sure if this is right or wrong.

[pnn44794]

It's wrong.  It should be 915.  For some reason, it's not excluding rows where Service Ordered is like '*DBaaS*' and there's no value for CCB_PROD_KEY3.

If you filter the data in Excel by Service Ordered like DBaaS and Change Type = Install, you should get 27 rows.  The two rows that have a value in CCB_PROD_KEY3 get excluded.  The 25 remaining rows without a value in CCB_PROD_KEY3 do not get excluded.  Very weird.

[sunny_talwar]

Before I go into the Excel, can you tell me what is wrong here

940 is composed of these guys.... I don't see '*DBaaS*' in Service Ordered and I don't see nulls in CCB_PROD_KEY3. Can you tell me which of the 7 rows should not have been included in to get 919? Is it the 1st row with 9 and 5th row with 12 (there sum is 21)

[sunny_talwar]

Filtering the raw data with the above conditions are giving me 940 rows. I have attached the 940 rows that I get. Can you tell me which ones are the wrong ones

[pnn44794 · Accepted solution]

Ok.  I'm not sure how to say this, but it looks like my issue is solved.  I don't know if the memory on my desktop got corrupted that Qlikview was using or if there was some other desktop issue causing some kind of problem or what.  But I needed to close out of all my apps and reboot my virtual desktop.  When my desktop came back up and I restarted Qlikview, the expression was working.  I don't know why.  I was not getting any error messages before.  I don't know what the deal was, but the expression is working now.

I'm sorry to have wasted your time, but I didn't know.  When I see something like this again, I'll close out of everything and reboot to make sure that's not the issue.  Have you ever heard of something like this happening before?

Thank you for your time.

[sunny_talwar]

You don't have to be sorry, but I am still confused as to how did you managed to get 919? I mean my computer has been restarted and not having any memory issues... why am I still seeing 940? Also, the Excel says 940 for me? I thought based on the database you think the answer should be 919, right? So, did the database say the answer needs to be 940? if that is true than how is this a QlikView problem? I would love to know this as based on what I think, you might be unnecessarily blaming system resources + QlikView when the fault was clearly resources + database

[pnn44794]

So the 919 number is due to the data being refreshed in the dashboard.  The data I attached above and which you used is stale.  With that said, I made a mistake when manually analyzing the data in the attached spreadsheet.  You are correct in that the number should be 940.  I mistakenly, after filtering the data, manually subtracted the 25 rows with no value for CCB_PROD_KEY3 to get to 915.  I should not have done that.  So, my fault on that.

I have had in the past, an issue with my virtual desktop where Excel functions, Outlook, etc, would start behaving abnormally.  I saw this last week and thus did the reboot.  I didn't believe Qlikview was at fault, but rather I believed I a had desktop issue.