[Canvas] Struggles to apply division to table row

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:

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

I think you are very close with the approach of using the ply function, but your issue is that the expressions expect either exactly one row or multiple rows- you can't combine multi-row tables with other multi-row tables unless you do something clever such as using filterrows to reduce the number of MAU rows to one.

This kind of division is not something Elasticsearch is great at, because Elasticsearch doesn't support joins. The limitation you are seeing might indicate that you need to process your data to be searchable.

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