Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm dying for some help with an expression I've written in a chart. The field has mixed values, some are only numbers and others also contain a letter in the beginning. Like this:
500020
D345737
But there is one value that is causing me headaches; D000158. I want to eliminate that value but it simply will not work!
The expression is:
if(Orderklantnummer=('D000158'),Null(), Sum({$<OrderJaar={$(=Only(OrderJaar))}>}Bedrag))
If I use the value 500020 or D345737 here it works oke, but as soon as I put the value I need in nothing happens.
Hope someone can illuminate me here because I have just been starting to work with QV (11)
Hi,
This expression
=Sum({$<Orderklantnummer-={'D000158'}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)
works as you expected, qlikview shows error for -=, but it works.
From Qlikview help file.
sum( {$<Product += {OurProduct1, OurProduct2} >} Sales )
for excluding
sum( {$<Product -= {OurProduct1, OurProduct2} >} Sales )
returns the sales for the current selection, but using an implicit union to add the products “OurProduct1” and “OurProduct2” to the list of selected products.
Hope this helps you.
Regards,
Jagan.
did you try using Orderklantnummer inside the set statement like.....
Sum({$<OrderJaar={$(=Only(OrderJaar))}, Orderklantnummer={"<>D*"}>}Bedrag)
this will exclude all Orderklantnummer that have a preceding letter D.
The problem is that I only want to exclude this number. I tested it with another nr that has a D in front and that worked. So I think the zeros are causing the problem. Thanks for the help, any more suggestions?
Hi,
Try this expression
=Sum({$<Orderklantnummer-={'D000158'}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)
Regards,
jagan.
if its only one value then you can exclude it as mentioned in the previous mail. That should work just as fine as substituting the exclusion with an alternative like below.....
Sum({$<Orderklantnummer={"<>D000158"}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)
Jasleen,
Your last suggestion did the trick! Thanks.
The solution that Jagan gave also eliminated some of the values, but left a syntax error in the formula (the red curly line under the formula no?). Thanks for the help too.
Sorry, I was too soon with my joy. The solution you gave seems to exlude all possibilities containing a D-Value anyway
Regards,
Jan
Hi,
This expression
=Sum({$<Orderklantnummer-={'D000158'}, OrderJaar={$(=Only(OrderJaar))}>}Bedrag)
works as you expected, qlikview shows error for -=, but it works.
From Qlikview help file.
sum( {$<Product += {OurProduct1, OurProduct2} >} Sales )
for excluding
sum( {$<Product -= {OurProduct1, OurProduct2} >} Sales )
returns the sales for the current selection, but using an implicit union to add the products “OurProduct1” and “OurProduct2” to the list of selected products.
Hope this helps you.
Regards,
Jagan.
Jagan,
You are right, this works fine. Thanks for your help.
Regards,
Jan