Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone! Thank you so much for taking the time to read my question and potentially answer my question! I have a very basic overview, type of question:
What is the resulting difference between the following two expression?
=sum(if(x>5, countOfFruit))
OR
=if(x>5, sum(countOfFruit))
?
So if I have a list of fruit and the various numbers of each specific fruit, which would be the correct was of summing all the fruits that had a greater value than 5?
Thank you!
I will try my best
=sum(if(x>5, countOfFruit)) //This will return as Sum(countOfFruit) Works and it show the Sum of Total
OR
=if(x>5, sum(countOfFruit)) // This will return as Sum(countOfFruit) Works and it won't show the Sum of Total
Instead of that, I would highly call this
Sum({<x = {'>5'}>}countOfFruit)
For better understand
Best option would be to use set analysis
sum({<x={'>5'}>}CountOfFruit)
Second best would be to use sum(if(x>5,CountOfFruit))
Please go through the link
When is it best to use sum(if()) over Set Analysis in an expression?
Thank you!
So the first option ( 'sum(if(x>5,....))' is the old version of set analysis?
What if I had three columns, one that fruit at time A (fruitA) and then fruit at time B (FruitB) -> how would I be able to say that I would only like the expression to sum the values when FruitA does not equal FruitB?
So :
FruitA FruitB CountOfFruit (want in sum)
Apple Apple 6 (no)
Pear Apple 10 (yes)
Pear Pear 7 (no)
Apple Pear 8 (yes)
So I would want it to return value as 18. Would it be (if I were to use set analysis):
= sum( {<FruitA<>FruitB>} CountOfFruit)
?
Thanks!
You would need to create a new field in the script which uniquely identify each row
LOAD RowNo() as Key,
FruitA,
FruitB,
CountOfFruit
FROM ....;
and then this
=Sum({<Key = {"=FruitA <> FruitB"}>} CountOfFruit)
or you can create a flag in the script
LOAD FruitA,
FruitB,
If(FruitA = FruitB, 1, 0) as Flag,
CountOfFruit
FROM ....;
and then this
=Sum({<Flag= {0}>} CountOfFruit)