1 Reply Latest reply on Nov 13, 2018 4:34 PM by Hari Ankem

    Joining two data sets and mapping a measure from each on dual axis over time series.

    Daniel Porter

      Hi all - I have tried to describe the situation to the best of my ability below, please let me know if you need additional details.




      Each dataset has it's own date field attached to their respective measures, over a time series of 30 years, but they are sporadic days so do not match up between datasets.


      I originally did a full outer join by project ID (my key field in each data set) and by the respective date of each set (lets call them date 1 and date 2). I then added a calculated field that said if date 1 was null, to retrieve date 2, else retrieve date 1. I called this a consolidated date and used it as my time series.



      It worked in some cases however the two issues I had where:


      1. The measure in the first dataset needed filtering by a dimension from the same dataset. This then filters the second measure (only when the dates have aligned and pulled the second measure onto the same line in the joined data).


      2. The measures (at least the second measure) is duplicating when the project ID and dates match up.



      I hope this was clear enough to explain the issue. I'm not able to post my datasets as it is protected by my firm but if my description isn't good enough I can try to mock up an example.


      Thanks for the help all!