Finding the latest year in multiple categories
Here is my problem: I have a number of countries and some sporadic data over the years. My challenge is to isolate the latest data point as shown below.
The first thing I want to do is bring in my measure, in this case ‘Percentage’, to the filters shelf. It doesn’t really matter how this filter is configured as we are about to change it.
The next step is to turn this from a green, continuous filter to a blue, discrete filter. To do this, right click on the ‘Percentage‘ pill in the filters shelf and click ‘Discrete’.
Now to configure this filter we want to ‘Exclude’ the ‘Null’ values as shown:
Next we need to create a calculated field which is simply ‘Last()=0’. What this is going to do is look for the last possible value in our table. We are going to configure this in just a moment.
We now want to move this new field to our filters shelf. Again this doesn’t really matter how we configure the filter as this is going to change in our next step. When you drag this field on you will see a small delta symbol (Δ) to the right of the pill, this shows that it is a table calculation and that means we can configure how it is computing.
Currently it is computing using the ‘Table (across)’ but we want this to compute by the ‘Year’ field. To do this, right click on the pill in the filters shelf, go to the compute using sub menu and click ‘Year’.
Once you have done this you will be able to use the last year that is available for the different countries: