1 Reply Latest reply on Jul 28, 2016 12:53 PM by Patrick Van Der Hyde

    Lookup different values across data sources

    Brock Ford



      I am trying to create a roll rate, but it uses the current value from field, and the previous value from a different field.


      I have two dimensions, called Tran Mia Grp and MIA_N_FINAL, and one measure called "num pol".

      Tran Mia Grp is using the current "num pol" value for "3 MIA to 4 MIA", and MIA_N_FINAL needs to use the previous value for "3 MIA (#)".


      So essentially I need to filter on Tran Mia Grp to get "3 MIA to 4 MIA" and filter on MIA_N_FINAL to get "3 MIA (#)".

      I can then use a lookup to find the previous value for MIA_N_FINAL and do a simple calculation using "number of pol".


      This was fine when calculated it once, but when I need to do it for 5 different values of Tran Mia Grp and the matching 5 different values of MIA_N_FINAL.


      I create 5 sheets to filter on each of the relevant values, and got the desired results, I am just trying to create a graph of those 5 different Time Series.


      I cannot attach a packaged workbook, due to privacy issues, and I am not sure I could replicate this using a different data source.


      I have attached screenshots of each of the 5 worksheets, with the values I need, and a picture of the graph I previously used in Excel, as well as the calculations I used and what I would like to replicate in here.


      Thank you very much.


      I hope I have made sense.