Mariana1234
New Member
Hi,
I have a dataset for several countries, from 1990 to 2014, with several variables. The latest year for which data are available varies according to the country and variable. The data is attached for you to see the format (it is in list/database format).
I would like to know how to create a variable that gives me the latest data point for which data are available, and a second variable with the year to which that data point refers to. I managed to do so with a lookup formula when the years are in columns (rather than in rows), do not manage to get it to work with the data as a list.
This would be useful as I use pivot tables, and would like to avoid the year to be one of the dimensions in the pivot. For example, I am analysing the Greenhouse Gas emissions per sector (energy, transport, etc), and per country. I would like to be able to have the countries in rows in the pivot, the sector as columns, and the variable "latest value available" in Values (since for some countries data are not available for 2014 for example, filtering for the latest year does not work).
Any help would be much appreciated! Happy to provide clarifications if needed.
many thanks!
Mariana
I have a dataset for several countries, from 1990 to 2014, with several variables. The latest year for which data are available varies according to the country and variable. The data is attached for you to see the format (it is in list/database format).
I would like to know how to create a variable that gives me the latest data point for which data are available, and a second variable with the year to which that data point refers to. I managed to do so with a lookup formula when the years are in columns (rather than in rows), do not manage to get it to work with the data as a list.
This would be useful as I use pivot tables, and would like to avoid the year to be one of the dimensions in the pivot. For example, I am analysing the Greenhouse Gas emissions per sector (energy, transport, etc), and per country. I would like to be able to have the countries in rows in the pivot, the sector as columns, and the variable "latest value available" in Values (since for some countries data are not available for 2014 for example, filtering for the latest year does not work).
Any help would be much appreciated! Happy to provide clarifications if needed.
many thanks!
Mariana