Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Set analysis looks good to me... are you sure it is not getting excluded?
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?
What id CCB_PROD_KEY3 and how is it related to [Service Ordered]?
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.
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?
Sorry. Typo above. My fault. It is CCB_PROD_KEY3. I'll edit my post to reflect the same.
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
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')
Hello Robin:
The expression you posted above gives me the following error: "Error in set modifier expression"
Are the * supposed to be there?