Set expression and no zero selection

[TomBond77]

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

[canerkan]

The Field [Goods Receipt] does not have any 'X' and there is no dataset where [Delta Goods Receipt] is greater than 0. So the formula might work correctly but your Data does not match any of the conditions in your expression. How do you know that in PO 1000 [Delta Goods Receipt] is 1 when your data is showing 0 and how would your expression know if the data is showing a different value. What is your delimiter for decimal numbers? Is it 0.0 oder 0,0?

[TomBond77]

Delimiter is 0.0

The Field [Goods Receipt] does not have any 'X' and there is no dataset where [Delta Goods Receipt] is greater than 0. So the formula might work correctly but your Data does not match any of the conditions in your expression.

Answer: Please check again the attached file, now with Goods Receipt 'X'. 

How do you know that in PO 1000 [Delta Goods Receipt] is 1 when your data is showing 0 and how would your expression know if the data is showing a different value.

Answer: Without set expression it shows 1, and this result is correct as this is a calculation of column H and I, the difference. Calculated beforehand.

[TomBond77]

with attachment

[phapalesaurabh]

Hello @TomBond77 ,

I think this example is of possible exclusion. You can try below set expression.

Set Expression -

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

[canerkan]

Did you check in your load script the settings for Decimal Separators?

And i am assuming you want to sum PO 1000, 1001, 1002? Then you need to change your formula to that, because the values are negative:

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

[TomBond77]

Probably I did explain my wished result completely wrong, sorry guys.

I would like to exclude all records with [Delta Goods Receipt]<=0 from the table. They should be hided. The consumer don't need to see these records in the table.

[canerkan]

You can set conditions in your table to do that. For example on your dimension "PO" see screenshot attached. Unfortunatly it in german but you should be able to see where you can set these conditions

 

[canerkan]

I forgot to mention that these conditions only work with the first column that contains an expression. So you need your column [Delta Goods Receipt] to be the first column that contains an expression

[TomBond77]

Okay, the column "Delta Goods Receipt" is now my first column. The formula on it is:

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

For all dimensions thereafter I have flaged "Include Null Values". 

Result now: Set expression is not working anymore, it ignores the 'X' of goods receipt and also incorrect the value of column A as given in the attachment. Column B shows the base measure without set expression, it shows the right value. Column A is the formula with set expression above mentioned.

[canerkan · Accepted solution]

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