Aggregate and count function

[danimelo1]

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?

[vishsaggi]

May be this you are looking for:

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

[danimelo1]

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

[vishsaggi]

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

[danimelo1]

Example data:

Actions:
LOAD * Inline [
"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:

Thanks in advance!

[vishsaggi]

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))

[danimelo1]

Not working.

[vishsaggi]

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.

[danimelo1]

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

if not. The data:

Actions:
LOAD * Inline [
"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

[vishsaggi]

my email: vishnoreply2017@gmail.com