0 Replies Latest reply on Sep 30, 2018 5:29 AM by Nicholas Booker

    Join calculation replacing date range with calculated column

    Nicholas Booker

      HI! Please help.

       

      Referring to my attached .twb file Sheet/field names...

       

      I want to show some data GER "GER" by Per Capita Income instead of by Year. eg. So I can generate a table where instead of showing India reach "3%" in "2015" I can show it reached "3%" when "Per Capita Income" was "1500".

       

      I have two data sources.

       

      One is organised Country/Year with the measure "GER" data itself.

       

      The other is one I created with "Country"/"Per Capita Income" with the measure "Year".

       

      I've joined the two data sources by "Country" but now (I think) need to add a calculated field column which MATCHES the date from the first table with the second (or finds the nearest date) and then shows the "Per Capita Income" from the appropriate HEADER row for nearest date.

       

      eg. in the first row China would show 1500 because the nearest date in this row that matches GER Year 1975 is Per Capita Income 1500.

       

      I used to do this via VLOOKUPs and MATCH and nest IFERROR in excel but excited by the Calculated Field option and really would appreciate help working this out.

       

      Thank you!