# App Development

Announcements
QlikWorld happening right now! You can still join: REGISTER NOW
cancel
Showing results for
Did you mean:
Creator

## Aggregate and count function

Hi experts,

From the table above, I want to count the number of active members. I'm trying with this expression but seems it doesn't works in my KPI label:

=aggr(count{<[S]={'3'}>}(max(date([Start Date]))distinct [User Sys ID]),[User Sys ID])

This is my table:

The count should be equal to 2 (User 8 and 9). The tricky part is that if a User Sys ID has S=3 in the last start date, it is an active user.

I think this is a good approach but I can't figure out how to implement it.

Any idea?

1 Solution

Accepted Solutions
Champion III

Can you try this?
Actions:
"User Sys ID","Start Date",S
8,1/24/2014,0
8,1/24/2014,3
9,7/1/2013,3
9,6/12/2016,1
9,12/10/2016,3
13,5/1/2014,3
13,7/1/2015,3
13,11/1/2015,3
13,11/25/2015,3
13,7/1/2016,0
13,7/1/2016,3
90,1/1/2014,3
90,4/10/2017,0
];

LEFT JOIN(Actions)
Date(Max([Start Date])) AS MaxStDt
Resident Actions
Group By [User Sys ID];

In your chart add [User sys ID] as dimension and expression as
= Sum(If(S = 3 and [Start Date] = MaxStDt,1, 0))

10 Replies
Champion III
May be this you are looking for:

=Count(aggr(max({<[S]={'3'}>} date([Start Date]), [User Sys ID]) DISTINCT [User Sys ID])
Creator
Author

Didi you mean this: =Count(aggr(max({<[S]={'3'}>} date([Start Date])), [User Sys ID]))

In your answer there is something wrong: Aggr needs 2 values and one ')' is missing.

Anyway, didn'r work

Champion III
Probably missed a bracket. So can you share some sample data with expected output, then i can work on it.
Creator
Author

Example data:

Actions:
"User Sys ID","Start Date",S
8,1/24/2014,0
8,1/24/2014,3
9,7/1/2013,3
9,6/12/2016,1
9,12/10/2016,3
90,1/1/2014,3
90,4/10/2017,0
];

The expexted output:

Champion III
Not sure if there is another good way of writing this but you can try below:

= Sum({<SVal = {3} >}If(Aggr(Count(Aggr(max([Start Date]), [User Sys ID])), [User Sys ID]) = 1, 1))
Creator
Author

Not working.

Champion III
Can you share a qvw file or a sample data file and highlight where it is not working. With the sample data set you provided it works at my end with that expression.
Creator
Author

Could you give me your email and I'll share my .qvf file from cloud.

if not. The data:

```Actions:
"User Sys ID","Start Date",S
8,1/24/2014,0
8,1/24/2014,3
9,7/1/2013,3
9,6/12/2016,1
9,12/10/2016,3
13,5/1/2014,3
13,7/1/2015,3
13,11/1/2015,3
13,11/25/2015,3
13,7/1/2016,0
13,7/1/2016,3
90,1/1/2014,3
90,4/10/2017,0
];```

Thanks

Champion III