Hi, so I'm struggling to make Daily active users / Monthly active users chart. The difficult thing is that I dont have that metric counted already and I need to count it by filtering out the unique users per day and per month. Then I need to divide those numbers. For example, Today we got 2 active users and monthly active users is 12 so DAU/MAU ratio will be (2/12)*100 = 16,67%.
So to get DAU I use this ELASTICSEARCH SQL querry:
SELECT Count(Distinct ProductLicenseKey) as cnt FROM "logsystem" Where "Time" >= CURRENT_TIMESTAMP() - INTERVAL 1 DAY
To get MAU active users I use:
SELECT Count(Distinct ProductLicenseKey) FROM "logsystem" Where "Time" >= CURRENT_TIMESTAMP() - INTERVAL 1 Month
And i calculated the DAU/MAU ratio in the view because Elastic SQL doesn't let you do multiple select and then divide it. Thats how i done it:
filters
| essql
query="SELECT Count(Distinct ProductLicenseKey) as userPerDay FROM \"logsystem\"
Where \"Time\" >= CURRENT_TIMESTAMP() - INTERVAL 1 Day"
| math
{string "100 * first(userPerDay)/" {filters | essql query="SELECT Count(Distinct ProductLicenseKey) as userPerMonth FROM \"logsystem\" Where \"Time\" >= CURRENT_TIMESTAMP() - INTERVAL 1 Month" | math "first(userPerMonth)"}}
| formatnumber "0%"
| render containerStyle={containerStyle backgroundColor="#3f9939"}
So now I'm stuck on calculating the DAU/MAU ratio and showing it in chart by day.
Any suggestions how can I approach to make it?
UPDATE:
tried to do that with canvas and ply function, but still no luck.
There is more information what I trying to achieve:
I have table daily Active Users:
time userPerDay
2020-04-07T03:00:00+03:00 3
2020-04-08T03:00:00+03:00 2
2020-04-09T03:00:00+03:00 1
2020-04-14T03:00:00+03:00 2
2020-04-15T03:00:00+03:00 4
2020-04-16T03:00:00+03:00 4
2020-04-17T03:00:00+03:00 5
2020-04-20T03:00:00+03:00 4
Then I have Monthly Users Count:
time userPerMonth
2020-03-09T02:00:00+02:00 3
2020-04-08T03:00:00+03:00 7
And what i want to get should be something like:
time userPerDay
2020-04-07T03:00:00+03:00 3/7 = 0.42
2020-04-08T03:00:00+03:00 2/7
2020-04-09T03:00:00+03:00 1/7
2020-04-14T03:00:00+03:00 2/7
2020-04-15T03:00:00+03:00 4/7
2020-04-16T03:00:00+03:00 4/7
2020-04-17T03:00:00+03:00 5/7
2020-04-20T03:00:00+03:00 4/7
Maybe anyone knows how I can make that table in canvas?