5 Replies Latest reply on Dec 24, 2018 9:00 AM by Yuriy Fal

    Record Comparison: How to compare user-selected 'n' most recent records to previous 'n' records

    James Jeffrey

      Issue: I need to analyze the performance of blog posts against 7 specific measures. However, this analysis requires the user to select the number of ('n') blog posts to evaluate and then compare that selection of 'n' most recent vs. previous 'n'. For example, if the user selected 3, the analysis would need to compare the 3 most recent blog posts based on publish date against the previous 3 blog posts published directly before that and visualize the difference.


      Current Solution: I have created two moving sum table calculations that interact with the user select parameter to capture the 'most recent' and 'previous'. The 'Most Recent' parameter is simply an integer the user selects to determine how many blog posts to analyze. 'Visits Depth 2 or Greater' is 1 of the 7 metrics I will need to analyze.


      Window Sum calculation to capture 'most recent' blog posts.


      WINDOW_SUM(SUM([Visits Depth 2 or Greater]), -[Most Recent],0)


      Window Sum calculation to capture 'previous' blog posts. The idea here is to shift the window the calculation evaluates to start directly after the most recent position.


      WINDOW_SUM(SUM([Visits Depth 2 or Greater]), -[Most Recent]+-[Most Recent],-[Most Recent])


      I can then find the comparison which is what I will visualize by subtracting the 'Most Recent' from the 'Previous'.


      [Visits Depth_Moving Sum Recent] - [Visits Depth_Moving Sum Previous]


      Issues: This is working; however, I need to create 3 calculated fields for each of the 7 metrics to and then use them in one worksheet so performance is taking a hit.


      Help Needed:

      1. A more elegant parameter/calculated field combination that isolates and sums the 'n' most recent blog posts and previous 'n' blog posts directly before that. If I can cut the need to duplicate this approach for each metric, that would greatly improve performance.
      2. A way to limit the most recent to the user selected number. Currently, the Window Sum calculations include the current value + the user selected number. So, if the user selects 3 it is really 4 records of data.
      3. A filter (possibly Index) to limit the visual to the latest difference to visualize one bar per metric.