Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
TomBond77
Creator III
Creator III

Set expression and no zero selection

Hi experts

The following formula is not working. I need only records with Delta Goods Received <> 0. Even the "set expression" at this stage is not working.

sum({<Year={'2021'},[Goods Receipt]={'X'}>} [Delta Goods Received] <>0)

Any ideas?

Tom

1 Solution

Accepted Solutions
canerkan
Partner - Creator III
Partner - Creator III

Your Column [Delta Goods Receipt] does not have to be the first of all columns.. just the first one with an expression (formula). So you can feel free to put some dimensions first and then [Delta Goods Receipt].  After you limited the rows of your Table you wont need to use set expression if your only intention was to excplude the null values. If you still want to make some calculation you will have to revaluate which conditions you need to use.

I have attached an example .qvf

Check column "PO" to see how to use the limitations of the table to exclude Values < 0. And also check column [Delta Goods Receipt] i deleted the whole set expression and just used a simple sum function

View solution in original post

21 Replies
canerkan
Partner - Creator III
Partner - Creator III

Hi TomBond77,

maybe try this:

sum({<Year={'2021'},[Goods Receipt]={'X'}, [Delta Goods Received]-= {0}>} [Delta Goods Received])

Regards,

Can

TomBond77
Creator III
Creator III
Author

Thanks, but there seems to be an error:

Sum({<Year={'2021'},[Goods Receipt]={'X'} [Delta Goods Received]-= {0}>} [Delta Goods Received])

TomBond77_0-1622129106869.png

 

TomBond77
Creator III
Creator III
Author

a comma was missing:

Sum({<Year={'2021'},[Goods Receipt]={'X'},[Delta Goods Received]-= {0}>} [Delta Goods Received])

But it is still not working. There are more than one measures in that table, but this shouldn't be a reason?

canerkan
Partner - Creator III
Partner - Creator III

Is it showing nothing or wrong results or an error?

Do you have sample data you could share by chance?

TomBond77
Creator III
Creator III
Author

It is showing wrong results. The set expression is completely ignored. The result shows record with "Goods Receipt" with flag "X" and without flag. It also shows records with "Delta Goods Received" = 0.

any ideas?

canerkan
Partner - Creator III
Partner - Creator III

Can you provide sample data so i can check on that? 

TomBond77
Creator III
Creator III
Author

Good Morning

Now I am bit further, the set expression for "Goods Receipt" = 'X' is active. I must correct my initial requirement. We need only records for the measure "Delta Goods Receipt" > 0 (greater than).

Sum({<Year={'2021'},[Goods Receipt]={'X'}, [Delta Goods Received] =- {0}>} [Delta Goods Received])

Here is a set of sample data, I need only records with Delta Goods Receipt > 0

TomBond77_0-1622183772789.png

 

Any ideas?

canerkan
Partner - Creator III
Partner - Creator III

If you only need [Delta Goods Receipt] > 0 then you can change your formula to that:

Sum({<Year={'2021'},[Goods Receipt]={'X'}, [Delta Goods Received] = {">0"}>} [Delta Goods Received])

Can you upload an xlsx or csv file of sample data so i can see how the data looks? I need to have an idea of how your data is put together to check if there are any inconsistencies in the expression.

TomBond77
Creator III
Creator III
Author

I used your formula Sum({<Year={'2021'},[Goods Receipt]={'X'}, [Delta Goods Received] = {">0"}>} [Delta Goods Received]) but it is not working and also ignoring the set expression [Goods Receipt]={'X'}

Furthermore it changes the value for [Delta Goods Received]. In the sample data for PO 1000 "Delta Goods Receipt" shows 0. But in fact it is 1.

Any ideas?