7 Replies Latest reply on Jan 14, 2019 5:04 AM by Michael Franzidis

    Rolling Window of a ratio

    Michael Franzidis



      I have the attached Tableau workbook which contains, for each property for each month, the NOI and PIC.


      I would like to calculate two things:

      1) 12 month rolling NOI, each month

      2) 12 month rolling NOI/PIC each month i.e. each month take the NOI and divide it by the PIC, and then sum this ratio each month over the past 12 months.


      I have also attached an Excel workbook which contains the raw data, as well as an example of the aggregated data for the 12 months ending 30Nov2018 to help explain what I'm trying to do.


      I've tried using the WINDOW_SUM function, but it isn't working as I'd expect it to. My reason for doing the aggregation in Tableau rather than directly in the dataset is that I am trying to retain the ability to aggregate dynamically across various dimensions (e.g. rather than market, I may want to aggregate over state).


      Any help would be appreciated!