topic Re: Aggregate and count function in App Development

Aggregate and count function

danimelo1 — Fri, 01 Feb 2019 20:04:39 GMT

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?

Re: Aggregate and count function

vishsaggi — Sat, 02 Feb 2019 04:46:32 GMT

May be this you are looking for:

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

Re: Aggregate and count function

danimelo1 — Sat, 02 Feb 2019 12:32:26 GMT

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

Re: Aggregate and count function

vishsaggi — Sat, 02 Feb 2019 17:08:28 GMT

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

Re: Aggregate and count function

danimelo1 — Sat, 02 Feb 2019 21:31:42 GMT

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!

Re: Aggregate and count function

vishsaggi — Mon, 04 Feb 2019 04:11:09 GMT

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

Re: Aggregate and count function

danimelo1 — Thu, 07 Feb 2019 14:40:53 GMT

Not working.

Re: Aggregate and count function

vishsaggi — Thu, 07 Feb 2019 14:50:25 GMT

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.

Re: Aggregate and count function

danimelo1 — Thu, 07 Feb 2019 22:50:08 GMT

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

Re: Aggregate and count function

vishsaggi — Thu, 07 Feb 2019 23:02:15 GMT

my email: vishnoreply2017@gmail.com

Re: Aggregate and count function

vishsaggi — Fri, 08 Feb 2019 04:22:14 GMT

Can you try this?
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
];

LEFT JOIN(Actions)
LOAD [User Sys ID],
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))