4 Replies Latest reply on Dec 29, 2016 7:39 AM by Kate Mahler

# Difference between two calculated fields

I have used a calculated field to get the results for my dataset IF they come from a certain week - and now I am trying to get the difference between these two fields, but when I write a calculation for it I end up getting 0. Would be very appreciative if someone could take a look and let me know what I am doing wrong!

Two calculations that work:

IF [Week]="This Week" THEN [Total Awareness] END

IF [Week]="Last Week" THEN [Total Awareness] END

Then this doesn't work: [Total Awareness (Last Week)]-[Total Awareness (This Week)]

I've also tried the approach on sheet 2 where I use a quick table calculation to get the difference, but I need to use this field to create another calculated field to edit the shape (if less than 0 it will be a down arrow, over 0 up arrow).

Any help would be greatly appreciated! Below is what I ultimately need it to look like

• ###### 1. Re: Difference between two calculated fields

Hi Kate,

This may work:

// TA Difference

ZN( [Total Awareness (Last Week)] ) - ZN( [Total Awareness (This Week)] )

And then please remove [Week] blue pill from Columns.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Difference between two calculated fields

That worked, thank you so much! Just so I understand what happened - what is ZN?

Thank you again!

• ###### 3. Re: Difference between two calculated fields

ZN returns an expression if it is not null, otherwise it returns zero. So if you've got a field with a bunch of null values in addition to actual values, wrapping an expression in ZN is a great way to make calculations work!

2 of 2 people found this helpful
• ###### 4. Re: Difference between two calculated fields

Makes sense, thanks for the explanation!