Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danimelo1
Creator
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:

Screenshot 2019-02-01 at 20.58.19.png

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
vishsaggi
Champion III
Champion III

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



View solution in original post

10 Replies
vishsaggi
Champion III
Champion III

May be this you are looking for:

=Count(aggr(max({<[S]={'3'}>} date([Start Date]), [User Sys ID]) DISTINCT [User Sys ID])
danimelo1
Creator
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

 

vishsaggi
Champion III
Champion III

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

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:

Screenshot 2019-02-02 at 22.30.25.png

 

Thanks in advance!

vishsaggi
Champion III
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))
danimelo1
Creator
Creator
Author

Not working.

vishsaggi
Champion III
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.
danimelo1
Creator
Creator
Author

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
Champion III
Champion III

my email: vishnoreply2017@gmail.com