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

    Lookup different values across data sources

    Brock Ford

      Hi,

       

      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.