# Complex SQL query calculated field generation in kibana
**URL:** https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356
**Category:** Kibana
**Created:** [December 22, 2020, 9:54am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356 "2020-12-22T09:54:17Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@anjali28](https://discuss.elastic.co/u/anjali28)
#### Post date: [December 22, 2020, 9:54am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/1 "2020-12-22T09:54:17Z")
Hello,
We want to created a visualization based on regular user in every month.
Here is the requirement:
"The visualization would display the number of regular users every month, regular users are those who have logged in on 5 different days in the same month (A user could log in several times on the same day, still would count as one)."
we have generated a sql query in MSSQL by below query:
Select loginyear,loginmonth,CustomerId, UserId, count(1) cutomerwiseregularuser
from
(
select distinct ru.CustomerId, ru.UserId,convert(date, ru.time) as logindate, MONTH(ru.time) loginmonth, year(ru.time) loginyear
from Regularuser as ru ) a
group by loginyear,loginmonth,CustomerId, UserId having count(1) \> 5
How can i achieve in kibana visualization to display into dashboard.
Please help
---
### Author:  [@AClerk](https://discuss.elastic.co/u/AClerk)
#### Post date: [December 23, 2020, 12:31am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/2 "2020-12-23T00:31:44Z")
Create a date field (i.e. timestamp without the time).
Then count distinct the usernames.
Filter out where count \< 5
Hope it will give you an idea
---
### Author:  [@anjali28](https://discuss.elastic.co/u/anjali28)
#### Post date: [December 23, 2020, 4:53am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/3 "2020-12-23T04:53:42Z")
can you please tell me where to create the date field and how to applie the query in kibana?
---
### Author:  [@AClerk](https://discuss.elastic.co/u/AClerk)
#### Post date: [December 23, 2020, 5:06am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/4 "2020-12-23T05:06:42Z")
Create it as a custom field under index patterns. Or better if you can do that before loading the data into your index, and create another field.
Then create a visualisation of your choice.
---
### Author:  [@anjali28](https://discuss.elastic.co/u/anjali28)
#### Post date: [December 23, 2020, 5:14am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/5 "2020-12-23T05:14:26Z")
We cannot create in index as that is from client side, so we have the option in custom field.
are you talking about scripted field in index pattern?
Also which function i can use to get distinct result for username?
Thanks
---
### Author:  [@AClerk](https://discuss.elastic.co/u/AClerk)
#### Post date: [December 23, 2020, 5:34am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/6 "2020-12-23T05:34:25Z")
yes scripted field.
you can use unique values when creating the visualisation.
---
### Author:  [@anjali28](https://discuss.elastic.co/u/anjali28)
#### Post date: [December 23, 2020, 5:45am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/7 "2020-12-23T05:45:54Z")
unique count gives the distinct count in each month userwise, then i am unable to check which user are my regular user according to above explain in MSSQL query.
Please we need those user who have login more then 5times in different days for the month.
how we can achieve in scripted field explain deep.
Thanks
---
### Author:  [@anjali28](https://discuss.elastic.co/u/anjali28)
#### Post date: [December 23, 2020, 7:19am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/8 "2020-12-23T07:19:56Z")
@TimV could you help me with the above case?
Regards
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [January 20, 2021, 7:20am UTC](https://discuss.elastic.co/t/complex-sql-query-calculated-field-generation-in-kibana/259356/9 "2021-01-20T07:20:02Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.