8 Replies Latest reply on Sep 20, 2017 7:43 AM by Shinichiro Murakami

    Counting Dates for Unique UUIDs and Showing % of Total within a Week

    David Shapir

      Hi Team -


      I have a few questions on how to work with data that includes unique UUIDs and a number of columns that either contain a date of completion or null value showing lack of completion. I have aggregated the number of unique UUIDs with a date within a particular week for various steps and now have the following questions:


      • How can I show the percent of people who completed Step A that also completed Step B as a percent of the total number people who completed Step A during a given week? I tried using the following formula but am getting an error saying one of my fields is not an aggregate (which is true): (WINDOW_SUM(if DATEDIFF('day',[Step B Date],[Step A Date]) < [Parameters].[Step A Date > Step B Date] then 1 else 0 end))/(Total(COUNT([Step A Date]))
      • Is there a way to have a relative date range that is the last 3 weeks for instance, excluding the current week? For example, I do not want to show the most current week of data unless the period is completed, but I may just have to write that into my query...


      Thank you in advance. Let me know if I can provide more info!