Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi TomBond77,
maybe try this:
sum({<Year={'2021'},[Goods Receipt]={'X'}, [Delta Goods Received]-= {0}>} [Delta Goods Received])
Regards,
Can
Thanks, but there seems to be an error:
Sum({<Year={'2021'},[Goods Receipt]={'X'} [Delta Goods Received]-= {0}>} [Delta Goods Received])
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?
Is it showing nothing or wrong results or an error?
Do you have sample data you could share by chance?
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?
Can you provide sample data so i can check on that?
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
Any ideas?
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.
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?