Hi Chris Beck,
Thanks for your reply. Yes, I did the same, but i dont want to see all the dates and null values.
I want to prepare a table, where changes (difference or % difference) will be displayed compared to last time only. i.e one value per Firm.
Here you are. There are multiple steps to this. I am walking you through all of them below. I have also attached a 10.1 workbook if you want to follow along.
Here is the finished product.
Our first step is to sort the date descending
Step 2 is to create the index() field. Notice that the index() field is between Firm and Date and is set to compute using specific dimensions. We need the index to be set for each date and firm.
Filter the index() on 1 & 2
Next, we need to build the difference calculation. Since the difference will be a table calculation, we are going to use the lookup function. And since we will be comparing the first date to the next date (first references the order in the table) we will use the offset of 1.
Last, hide index value 2 and hide the header for index field
what seems so strange? your data is sorted by date and in comparing your last circled area we are comparing 28 to 831 which is 97% lower. so that all looks good. dont worry about the percentages underneath as you will just hide index2 value.