12 Replies Latest reply on Jun 5, 2014 7:21 PM by Robyn Stables

# Help exclude data from a calculation please

I have attached an example file of what I need.

I have a table calculation with gives me the difference between data in a pane (down), however I don't want a difference if one of the cells has no data.

See the dashboard for an explanation.

Pleeease help, I am not the best programmer.  I know someone out there will find this really easy.

• ###### 1. Re: Help exclude data from a calculation please

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

I hope this is what u wanted. Use above formula.

• ###### 3. Re: Help exclude data from a calculation please

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

formula:- 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

1 of 1 people found this helpful
• ###### 5. Re: Help exclude data from a calculation please

Here 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.

1 of 1 people found this helpful
• ###### 6. Re: Help exclude data from a calculation please

Awesome, this is exactly what I needed, thank you so much for your speedy help.

• ###### 7. Re: Help exclude data from a calculation please

Thank you so much.  Perfect.

• ###### 8. Re: Help exclude data from a calculation please

Can you attach your workbook for me please, I can't see an attachment.

Thanks

• ###### 9. Re: Help exclude data from a calculation please

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

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.

1 of 1 people found this helpful
• ###### 11. Re: Help exclude data from a calculation please

Sorry. It is attached now in my original post.

1 of 1 people found this helpful
• ###### 12. Re: Help exclude data from a calculation please

This worked perfectly thank you.