# Reporting counts based on date criteria

**URL:** <https://discuss.elastic.co/t/reporting-counts-based-on-date-criteria/129573>\
**Category:** Kibana\
**Created:** [April 26, 2018, 2:09am UTC](https://discuss.elastic.co/t/reporting-counts-based-on-date-criteria/129573 "2018-04-26T02:09:04Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![kenneth](https://avatars.discourse-cdn.com/v4/letter/k/94ad74/32.png) [@kenneth](https://discuss.elastic.co/u/kenneth)\
**Post date:** [April 26, 2018, 2:09am UTC](https://discuss.elastic.co/t/reporting-counts-based-on-date-criteria/129573/1 "2018-04-26T02:09:05Z")

</div>

Hi,

I have this SQL table called UserHistory ingested in ElasticSearch. This table simply stores a record every time a user joins a group and when was that user an active member of that group based on the From and To date columns.

![image](https://us1.discourse-cdn.com/elastic/original/3X/c/b/cb1afeb4dcd661b202c5efe8edfbab2a0fdeb05b.png)

If I want to generate a report that would give me the number of users who joined a group since 1 April 2018, I could use this SQL statement

```
select uh.GroupName, count(1) [UserCount]
from UserHistory u
where '2018-04-18' between u.From and u.To
	and uh.HasLeftGroup = 0
	and not exists 
	(
		select 1 from UserHistory uh2 
			where '2018-04-01' between uh2.From and uh2.To 
				and uh2.UserId = uh.UserId 
				and uh2.HasLeftGroup = 0
	)
group by uh.GroupName

```

and gives me this result  
 ![image](https://us1.discourse-cdn.com/elastic/original/3X/8/e/8e960e82a81963d629873817067e5deb9a902631.png)

and, if I want to get the number of users who joined a group since 5 April 2018, I would get this result

```
 select uh.GroupName, count(1) [UserCount]
    from UserHistory u
    where '2018-04-18' between u.From and u.To
    	and uh.HasLeftGroup = 0
    	and not exists 
    	(
    		select 1 from UserHistory uh2 
    			where '2018-04-05' between uh2.From and uh2.To 
    				and uh2.UserId = uh.UserId 
    				and uh2.HasLeftGroup = 0
    	)
    group by uh.GroupName

```

![image](https://us1.discourse-cdn.com/elastic/original/3X/b/b/bb402b9477b74efbc42091e591ef7bcbbb820eeb.png)

Can we do the same in Kibana?

* * *

Cheers  
Kenneth

---

<div class="post-metadata">

**Author:** ![jbudz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jbudz/32/45922_2.png) [@jbudz](https://discuss.elastic.co/u/jbudz)\
**Post date:** [April 26, 2018, 8:46pm UTC](https://discuss.elastic.co/t/reporting-counts-based-on-date-criteria/129573/2 "2018-04-26T20:46:34Z")

</div>

Hey, can do. In the search bar we would put something like `From:<=2018-04-05 AND: Tc:>=2018-04-05 AND HasLeftGroup:0` After filtering we would use the data table visualization with a terms aggregation on GroupName.

---

<div class="post-metadata">

**Author:** ![kenneth](https://avatars.discourse-cdn.com/v4/letter/k/94ad74/32.png) [@kenneth](https://discuss.elastic.co/u/kenneth)\
**Post date:** [April 27, 2018, 4:10am UTC](https://discuss.elastic.co/t/reporting-counts-based-on-date-criteria/129573/3 "2018-04-27T04:10:25Z")

</div>

Thanks for the response - will try this.

Quick question - if you were to model the data before it gets ingested in elastic, is it more suitable to group these historical records into one document per user? Reason I asked because my filters could change depending on the need like say now I am doing a filter on 1 Apr 18, then 5 Apr 18 and so on...

I would like to create a common data table visualization where I can just change the filters

Doc\_User1  
{  
"user\_id": 1,  
"groups": [  
{  
"group\_name": "Group A",  
"from": "2018-03-16 16:30",  
"to": "2018-04-05",  
"has\_left\_group": "1"  
},  
{  
"group\_name": "Group B",  
"from": "2018-04-05 16:30",  
"to": "2018-04-18 09:26",  
"has\_left\_group": "0"  
}  
// ..... etc  
]  
}

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [May 25, 2018, 4:10am UTC](https://discuss.elastic.co/t/reporting-counts-based-on-date-criteria/129573/4 "2018-05-25T04:10:28Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.