
1. Re: Help exclude data from a calculation please
arup majumder Jun 3, 2014 10:00 PM (in response to Robyn Stables)if ZN(SUM([Data]))=0 then LOOKUP(ZN(SUM([Data])), 1)
else ZN(SUM([Data]))  LOOKUP(ZN(SUM([Data])), 1)
end
use this calculation

2. Re: Help exclude data from a calculation please
arup majumder Jun 3, 2014 10:02 PM (in response to Robyn Stables) 
3. Re: Help exclude data from a calculation please
Robyn Stables Jun 4, 2014 1:54 AM (in response to arup majumder)No this isn't what I was wanting. I want NO answer when one of the rows has no data. 26.00 and 22.00 are incorrect as they are being subtracted from 0. I want it to show nothing in this case.

4. Re: Help exclude data from a calculation please
arup majumder Jun 4, 2014 2:08 AM (in response to Robyn Stables)1 of 1 people found this helpfulformula: if ZN(SUM([Data]))=0 or ZN(LOOKUP(ZN(SUM([Data])), 1))=0 then null
else ZN(SUM([Data]))  LOOKUP(ZN(SUM([Data])), 1)
end

I hope it solves ur query. Plz reply

5. Re: Help exclude data from a calculation please
kettan Jun 5, 2014 9:45 AM (in response to Robyn Stables)1 of 1 people found this helpfulHere is a calculation that might do what you want:
IF NOT ISNULL(SUM([Data])) AND NOT ISNULL(LOOKUP(SUM([Data]), 1)) THEN ZN(SUM([Data]))  LOOKUP(ZN(SUM([Data])), 1) END
Actually ZN is not needed in line 2 because data is already validated in line 1 as having values in both.
See more in attached workbook.

6. Re: Help exclude data from a calculation please
Robyn Stables Jun 4, 2014 10:21 AM (in response to arup majumder)Awesome, this is exactly what I needed, thank you so much for your speedy help.

7. Re: Help exclude data from a calculation please
Robyn Stables Jun 4, 2014 10:23 AM (in response to kettan)Thank you so much. Perfect.

8. Re: Help exclude data from a calculation please
Robyn Stables Jun 4, 2014 4:03 PM (in response to kettan)Can you attach your workbook for me please, I can't see an attachment.
Thanks

9. Re: Help exclude data from a calculation please
Robyn Stables Jun 4, 2014 4:08 PM (in response to kettan)I also now want to filter out b and e so that there is no empty row, I can't seem to do this with the filters.

10. Re: Help exclude data from a calculation please
Siraj Samsudeen Jun 4, 2014 11:41 PM (in response to Robyn Stables)Hi Robyn,
You can create another calculated field to filter it. You can see the results here:
I have used a formula very similar to that of kettan. However, it is slightly different. So I am pasting it here for your reference:
[Diff to Prev Data] = IF NOT (ISNULL(ATTR([Data])) OR ISNULL(LOOKUP(ATTR(Data),1)) )THEN SUM(Data)  LOOKUP(SUM(Data),1) END
Now that this formula is done, we need to filter out C & E. So, logically if we take the above equation and put it on a filter to exclude Nulls, it should work. However, it does NOT work as it takes away the Year 1 for even A & D. So, our filter has to retain Year 1 in cases where both values are present, even though we will display the value in Year 2 only. So, that can be achieved with another calculated field Filter like this:
[Filter] = IF FIRST() == 0 AND NOT (ISNULL(ATTR([Data])) OR ISNULL(LOOKUP(ATTR(Data),1)) )THEN 1 ELSEIF NOT (ISNULL(ATTR([Data])) OR ISNULL(LOOKUP(ATTR(Data),1)) )THEN 1 ELSE 0 END
In Line 2, I am checking whether I am at the first record (Year 1) and if yes, then I am checking to make sure that the current record and the next record have both valid data. If both conditions are met, this should NOT be filtered. So, I return a 1. Then in Line 4, I am at the second record (year 2) and here I check if the current record and the previous record have both valid data. Otherwise, it means that one of them has a missing data and I return a 0 accordingly.
Here I have used ATTR(Data) as you have only one value per year and it would work. However, if you have more than 1 row in the datasource, It is better to use SUM(Data) than ATTR(Data) in the conditions. Please let me know if you are able to understand and use it.

File for help_SS.twbx 25.6 KB


11. Re: Help exclude data from a calculation please
kettan Jun 5, 2014 9:47 AM (in response to Robyn Stables)1 of 1 people found this helpfulSorry. It is attached now in my original post.

12. Re: Help exclude data from a calculation please
Robyn Stables Jun 5, 2014 7:21 PM (in response to Siraj Samsudeen)This worked perfectly thank you.