6 Replies Latest reply on Dec 6, 2018 3:40 PM by Yuriy Fal

    Divide a measure by itself with varying only one dimension/column

    Harley Chen

      Please see the attached example.


      The source data will typically contain 6-ish factors, and 1 data columns (Rate) that I want to apply the division to. The number of factors may change, and the levels within each factor may also change, e.g. Age will sometimes be quinquennial, sometimes 1-incremental, and starting and ending ages may also be different.


      For each factor, I want to create a sheet that will divide the (Rate) field by itself with the factor of interest offset by one.


      For example, in the Age sheet, the data in the grid should represent Age_(x+1)/Age_x, given that all the other factors are the same.  In essence, I am attempting to create a "heat map" that lets me quickly see the trend of the Age_(x+1)/Age_x movements. In here the (x+1) is technically referring the "next" age in the data, rather than actual x+1 age value.


      In this workbook, the workaround current is


           'IF(LOOKUP(AVG([Age]),1)>AVG([Age])) THEN LOOKUP(AVG([Rate]), 1)/AVG([Rate]) ELSE NULL END'


      However, it is not ideal since if you look at the Band sheet, it fails to produce what I want. Essentially, each factor, or dimension, should be in an order that I specify (e.g. smallest to largest, A to Z), and I want to compare the two adjacent levels of a dimension.


      Thanks so much in advance!