Date Problem Getting Just Year Value Without Duplicates

[pnn44794]

Hello:

I'm trying to get just the year value from another date field.  I can get just the year, but I wind up with "duplicates", meaning I have 2 choices for 2016 (data only goes back to November 2016), 12 for 2017 and two for 2018 (assuming Jan and Feb).  I'm using a straight table and I'm wanting to have just 3 values to choose from when clicking on the drop down arrow for Approval Completed Year, so just one 2016, one 2017 and one 2018.  I've attached sample data in case it's needed.  Approval Complete Date is the column I'm trying to get the Year value from.

The data is being loaded from an Excel file and I've confirmed Excel has Approval Complete Date (the field I want Year from) formatted as a Date field (see attached sample data).  The following is what I've tried from within the load script and the results.

Date([Approval Complete Date], 'YYYY') as [Approval Complete Year], <-- I get multiple 2016, 2017 and 2018 values, one for just about each row of the data

Date(MonthStart([Approval Complete Date]), 'YYYY') as [Approval Complete Year], <-- I get the results described in the first paragraph above - I've also tried this as the Dimension, with out of course the as part

Date(Date#([Approval Complete Date], 'MM/DD/YYYY'), 'YYYY') as [Approval Complete Year], <-- I get no Year results

Hopefully, I'm just missing something simple.  As always, any and all help is appreciated.  Thanks in advance.

[pnn44794 · Accepted solution]

Hello All:

First, thanks to anyone who may have taken the time to view this post.

The issue is resolved.  I determined that I needed  to use Year([Approval Complete Date]) as [Approval Completed Year] in my Load Script as opposed to any of the other three options I listed in my original post.  That gives me just the Year value and no duplicates, for example, in my straight table when I click the filter arrow for the Approval Completed Year column (I only get 3 choices, 2016, 2017 and 2018 and not two 2016 , 12 2017 and two 2018 choices).

Additionally, I had the same problem with formatting the date as YYYYMM.  To solve that problem, I used Date(MonthStart([Approval Complete Date]), 'YYYYMM') as [Approval Completed YearMonth] in my Load script as well.

If anyone should have any comments or feedback, please let me know.  Thanks.