Hi all,
With respect to the attached.
I'm trying to create a mini-model for currency exchange rates. The data is stored in our financial reporting software and is multi-currency and multi-period. Users of this information typically download from the system each time they need a specific rate on a specific date, hence a lot of time being lost to data extraction. The model I'm putting together aims to manipulate all historic data so that the user can quickly find the rate they want simply by making a couple of selections from the DV lists (yellow cells, worksheet 'Table).
As the data spans at least 3 years, I don't want to simply have one DV list containing all the days in these three years, as the user would have to sift through +1000 options to find their date. Hence, I have one DV list for the year selection, and another, dependent DV list that will show only the dates in the selected year.
The problem is this:
- The dates from the system are presented in a non-conventional format in (see worksheet 'System', column C)
- I have to reformat those dates to convert to a standard format (see column H)
- Once converted, those dates still don't appear to function like a normal date, so another conversion is needed (see column I)
- I then extract a list of unique years that appear in column I (see column Q) using the formula =UNIQUE(TEXT(SORT($J$3#,,-1),"yyyy")). Note that I am using the text function here because I don't know how else to achieve the same result. FYI this is the data source for the first DV list (see cell C11, worksheet 'Table')
- Navigating back to worksheet 'System' in column R, I attempt to extract a list of dates in column I, where the year matches the selection made in cell C11 of the 'Table' worksheet using the following formula =FILTER($J$3#,YEAR($J$3#)=Table!$C$11)
I suspect the root of the problem is that the data in column Q is formatted as text. I suspect this because if you replace the element of the above formula that I have presented in bold text, with the value 2022, then column R does then generates a list of all the dates in the year 2022.
As the root of the problem seems to be that I am converting to text in order to extract a unique list of years, I wonder if anyone can help find a better solution that doesn't generate errors down the road i.e. in column R
Thanks in advance,
A
PS I've had to drastically reduce the data set by hundreds of thousands of lines as it was too lare to upload. May or may not be relevant to know that.
With respect to the attached.
I'm trying to create a mini-model for currency exchange rates. The data is stored in our financial reporting software and is multi-currency and multi-period. Users of this information typically download from the system each time they need a specific rate on a specific date, hence a lot of time being lost to data extraction. The model I'm putting together aims to manipulate all historic data so that the user can quickly find the rate they want simply by making a couple of selections from the DV lists (yellow cells, worksheet 'Table).
As the data spans at least 3 years, I don't want to simply have one DV list containing all the days in these three years, as the user would have to sift through +1000 options to find their date. Hence, I have one DV list for the year selection, and another, dependent DV list that will show only the dates in the selected year.
The problem is this:
- The dates from the system are presented in a non-conventional format in (see worksheet 'System', column C)
- I have to reformat those dates to convert to a standard format (see column H)
- Once converted, those dates still don't appear to function like a normal date, so another conversion is needed (see column I)
- I then extract a list of unique years that appear in column I (see column Q) using the formula =UNIQUE(TEXT(SORT($J$3#,,-1),"yyyy")). Note that I am using the text function here because I don't know how else to achieve the same result. FYI this is the data source for the first DV list (see cell C11, worksheet 'Table')
- Navigating back to worksheet 'System' in column R, I attempt to extract a list of dates in column I, where the year matches the selection made in cell C11 of the 'Table' worksheet using the following formula =FILTER($J$3#,YEAR($J$3#)=Table!$C$11)
I suspect the root of the problem is that the data in column Q is formatted as text. I suspect this because if you replace the element of the above formula that I have presented in bold text, with the value 2022, then column R does then generates a list of all the dates in the year 2022.
As the root of the problem seems to be that I am converting to text in order to extract a unique list of years, I wonder if anyone can help find a better solution that doesn't generate errors down the road i.e. in column R
Thanks in advance,
A
PS I've had to drastically reduce the data set by hundreds of thousands of lines as it was too lare to upload. May or may not be relevant to know that.