Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to see if a Prime Name or relationship to a Master Name changes.
Below is the data:
Date | Prime Code | Prime Name | Prime City | Prime State | Master Code | Master Name | Broker Name |
Dec-16 | abcd | Aon Chicago | Chicago | Illinois | efgh | efgh | efgh |
Dec-15 | abcd | Aon Chicago | Chicago | Illinois | efgh | efgh | efgh |
Dec-14 | abcd | Aon Chicago | Chicago | Illinois | ijkl | ijkl | ijkl |
Here is what I want to see: For Dec 2016, the names stayed the same so the result is True, For Dec 2015 the names stayed the same so the results are still True, but in Dec 2014, the Master Name, Code and Broker Name changed so the result is changed to False.
I tried something like this but it didn't work: =IF(([Business Cycle Dt] = Max([Business Cycle Dt]) and [Business Cycle Dt] = '2016-12-31') and ([Prime_Producer_Name]=[Prime_Producer_Name])),'True', 'False'
as of Dec 2016 | as of Dec 2015 | as of Dec 2014 | |||||||||||
Prime Code | Prime Name | Prime City | Prime State | Master Code | Master Name | Broker Name | CYTD (2017)WP | Master Asscociation | WP | Master Asscociation | WP | Master Asscociation | WP |
abcd | Aon Chicago | Chicago | Illinois | efgh | Aon Midwest | Aon | 1000 | TRUE | 1200 | TRUE | 900 | FALSE | 1300 |
You need to compare in a table or script??
Hi,
You could do it in script if all the fields reside in the same table, first ordering by 'Prime Code' ASC, Date DESC, for example and later using Previous() function in order to detect if the same values are in the fields of the record of the following year.
I hope it helps you!
Regards,
H
In a table
like this. where it is true that is where the names didn't change.
as of Dec 2016 | as of Dec 2015 | as of Dec 2014 | |||
Master Asscociation | WP | Master Asscociation | WP | Master Asscociation | WP |
TRUE | 1200 | TRUE | 900 | FALSE | 1300 |