# How to get DAU / WAU / ... charts in ElasticSearch?

**URL:** <https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774>\
**Category:** Elasticsearch\
**Created:** [November 2, 2016, 9:18pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774 "2016-11-02T21:18:25Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![KIVagant](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kivagant/32/7182_2.png) [@KIVagant](https://discuss.elastic.co/u/KIVagant)\
**Post date:** [November 2, 2016, 9:18pm UTC](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774/2 "2016-11-02T21:18:32Z")

</div>

I thinking about this variants:

1. I need to replace this part with something like variables.

In MySQL it can be something like this: (in reality can't because of [the problem](https://gist.github.com/KIVagant/e143ac569a0cb2984d528495d2fcf6b0))  
SELECT @day := day as day, wau.visitors  
(SELECT visitors FROM ...impossible wau query... WHERE date=@day) as wau FROM (SELECT day FROM ...crazy query for calendar range... )

In ElasticSearch maybe it is possible [with scripting](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html)? But how?

```
... WAU aggregation inside scripted loop through a calendar executed for each day:
 "lte": "{{ CALENDAR_DAY_VARIABLE }}",
 "gte": "{{ CALENDAR_DAY_VARIABLE }}-6d/d"

```

1. I need to use [pipeline aggregations](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline.html). It is should be something like a "LEFT JOIN".

- On the left side – a calendar – date histogram, _but without using any index data_.
- On the right side – a WAU aggregation that should be called for each day. Some variable is needed again here.

1. I need to use pipeline aggregations again. In the nested aggregation level I need to make a calendar and get any dates from them. In the top aggregations level I need to get WAU aggregation for any date from nested level, using nested.day instead of "now" in "lte", "gte" and/or "interval" params.

Any ideas about this? Maybe someone already makes this charts with ES?

P.S.: I thinking about cardinally different variant then I can call the simple DAU / WAU / ... queries for every day on application level and cache the result inside a different ES index.

- The [related Logstash problem](https://discuss.elastic.co/t/how-to-get-es-aggregation-data-as-logstash-input/64776).

It should be much more faster of course and then I'll can use this index inside exists Kibana visualisations (especially inside Timelion).

---

_[View the full topic](https://discuss.elastic.co/t/how-to-get-dau-wau-charts-in-elasticsearch/64774)._