4 Replies Latest reply: Nov 2, 2015 1:22 PM by Thais Vazquez RSS

    Count aggregation that includes previous and current values

      Hi All,

       

      Data Explanation:

       

      I have 4 Type of Users:

               1.User,

               2.Technical,

               3.System,

               4.Not Assigned

       

      I have a column where says the number of times a specific type of user has ran a query in a specific month and year

      Now, I am trying to create an expression that counts the number of users that are equal to "User" that has executed a query the previous month and current month. Therefore, I am looking for repeat users. For example, Peter is a "User", he ran a query on Sept 2015 and Oct 2015 he will be count it as a repeat user.

       

      My script look like this:

       

      LOAD

          User,

          "User ID",

          "User Type",

          InfoProvider,

         "InfoProvider ID",

          "Query",

          "Query ID",

          "Query Type" as [Query Type Historical],

          date(date#("Cal. year / month",'YYYY-MM'),'MMM-YY') as [Cal. year / month],

          "Query Execution"

       

       

      This is the expression that I have used but it doesn't work:

       

      Count({<Month={"$(=today(2))-1,Month(today(2))}"},[User Type]={"User"}>}[Query Execution])

       

       

      Please help. Appreciate your time and help in this.

       

      TV