1 Reply Latest reply on Feb 22, 2018 4:44 PM by Ryan Blosser

    How to normalize "moving calculation" as a percentage?

    Ryan Blosser

      Hello,

       

      I am trying to create a vis with lines on a line graph that are "what percentage of records have at least this value".  I have Month as a continuous dimension on the x-axis (most months have representation, some don't).  I used the Moving Calculation (previous 0, next 200) to get the answer (basically summing up everything to the right, including the current cell, of where I currently am on this axis) but I want to normalize all my lines to 100%.  So I thought this would work:

       

      window_sum(sum([dummy]), index(), last())  /   total(sum([dummy]))

       

      (dummy was my way of getting 1 for every row)


      And month was my addressing column

       

      But the problem is that my dips do not align with expectation; that is to say, the percentage drops do not align with the record count drops.  For example, I should have big drops after 12 and 36 months.  I have this on the first method, but the second method drops in odd places.  The line stays flat in these areas.

       

      I will try to attach a workbook with sample data, but I thought someone might know from this explanation.