4 Replies Latest reply on Nov 28, 2018 1:00 PM by James Jeffrey

    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.
        • 1. Re: Record Comparison: How to compare user-selected 'n' most recent records to previous 'n' records
          Yuriy Fal

          Hi James,


          For a simple view -- just one Mark with Measure Names / Values -- i'd like to setup:


          1) a TopN Filter (for the x2 number of recent dates);


          2) a FIXED LOD expression using MEDIAN() aggregation (to split into two Date Bins);


          3) a number of Measures (as Quick Table Calculations --> Difference along Date Bins)


          4) a LAST()==0 Table Calculation Filter (to show only the recent Date Bin Mark).


          Please find the attached.

          Hope it could help a bit.




          1 of 1 people found this helpful
          • 2. Re: Record Comparison: How to compare user-selected 'n' most recent records to previous 'n' records
            James Jeffrey



            This worked and simplified my original approach. I just need to reframe the title of the visual so that the user understands how the parameter works and what data they are viewing (e.g., if you select 4, you are comparing the most recent 2 records against the previous 2 records for a TOTAL of 4 records).


            If I have any questions or further advancements to this analysis I will post for your knowledge.


            Thanks again!



            • 4. Re: Record Comparison: How to compare user-selected 'n' most recent records to previous 'n' records
              James Jeffrey

              Yuriy Fal


              I have a follow-up question that extends the simple view you created to include a time dimension. Let me know if you have any thoughts to what I have detailed below:


              Desired Solution: Using the DATE BINS you created, I need to show each bin over time for certain metrics. We can use the measure "Visits Depth 2 or Greater" as the metric test subject. The date dimensions to be used are DAY and DATE FIRST CREATED PUBLISHED. I need to show the metric "Visits Depth 2 or Greater" for each of the DATE BINS from the minimum DATE FIRST CREATED PUBLISHED for each PAGE NAME within the bins through the first 30 dates within DAY (even if there is no data for a DAY within the first 30). Remember the DATE BINS are showing the 'n' most recent blogs and 'n' previous blogs based on the user-selected parameter - # BLOGS (x2) - so this functionality would need to apply to this line chart over-time as well.


              Issues: I am having a difficult time getting each of the DATE BINS and respective PAGE NAMES to have the same starting point of 0 based on their minimum DATE FIRST CREATED PUBLISHED. If we can get each to have the same starting point of 0, we can then use DAY to show the first 30 days of activity for each bin.

              I've tried a number of approaches, so just curious if anything pops into your head. I've reattached your workbook with only the appropriate fields needed here. Let me know.