Count aggregation that includes previous and current values

[#1]

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

[Gysbert_Wassenaar]

Try this: Count({<[Cal. year / month]={">=$(=Monthstart(Today(2)))<=$(=MonthEnd(Today(2)))"},[User Type]={"User"},[User ID]=p({<[Cal. year / month]={">=$(=Monthstart(Today(2),-1))<$(=MonthStart(Today(2)))"},[User Type]={"User"}[User ID])>}[Query Execution])

[#3]

Hi,

Thanks for your help. I just tried to apply that expression into my application but it didn't work.

TV

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the problem? See this document for how to do that without spilling confidential information: Preparing examples for Upload - Reduction and Data Scrambling

[#5]

Hi,

Thank you for your response. I am not getting any type of result when I apply the following expression into my trend analysis chart :

Count({<[Cal. year / month]={">=$(=Monthstart(Today(2)))<=$(=MonthEnd(Today(2)))"},[User Type]={"User"},[User ID]=p({<[Cal. year / month]={">=$(=Monthstart(Today(2),-1))<$(=MonthStart(Today(2)))"},[User Type]={"User"}[User ID])>}[Query Execution]).

I just get a straight line in trend analysis chart. Do you know why this expression is not working in my application? Thank you  in advance.

Sincerely,

TV