Timestamps - I need to calculate how many users were active each minute, having single intervals of their activity

  • 28 April 2021
  • 3 replies


The problem I’m struggling is how to create an AMR (Average Minute Rating) using MAQL. For simplicity I started with one day period when I want to observe how users activity changes over time (for example minute by minute). So I have data of a form:

  • userID
  • start time - atribute refering to Start time table having M, H, S values as a facts, start time label as a key HH:MM:SS and an atribute start time [min] HH:MM
  • start day - date 
  • end time - atribute refering to table with a structure like Start time table already described
  • end day - date 
  • activity details

I was able to build a very inelegant vay to find out how many unique users were active during particular hour (e.g 20:45  HOUR = 20, MINUTE = 45), but with one major drowback which is that I need hour and minute as a variable to do those calculations. Here’s the MAQL form:

I need solution that could be visualised - for every minute, automatically. Hoping for your help! I would appreciate any hints :)


Best answer by Boris 5 May 2021, 09:37

View original

3 replies

Userlevel 3

Custom time dimensions can be added to your LDM and allow you to work with timestamps in metrics for your insights. This particular case is quite specific, so we would need to look into the project a bit further to see what is going on and which results are incorrect. We definitely would need more details as to what you are currently seeing for your results vs. what you expect to see.

Thank you for your response! I’ll try to provide more details.

Right now, calculation for that specific minute (20:45) seems to make sense – in the insight I can see the number, which is probably the right one. However, what I really need to do is to provide a metric that would check every minute during the day – from 00:00 to 23:59 (without need to set HOUR and MINUTE as variables like in the example I’ve given). Then, if I could assign such unique viewers numer to each minute a day I could visualize how users’ activity was changing over time during the day. And that’s the final output I’m hoping for.

I’ve read some of your articles about dealing with timestamps and using epoch time, so I rearrange my LDM model a bit. Here’s the time-activity part:

End/Start Time in Seconds has a range (0-86399) and analogously End/Start Time in Minutes a range (0-1439).

Now the condition for particular minute is simpler and more redable, however I still didn’t find a way to calculate unique viewers at once for every minute a day. Here I still need to set a single minute value (for 20:45 that would be 1245 minute value).

Is there some clever MAQL approach that would allow me to calculate it for every minute possible during the day not just for a predetermined single value (1245)? I need that for creating plot where those values would be visible.  Should I produce another auxiliary table? Please let me know what more details can I provide. 


Thank you so much for wanting to help me! 


Userlevel 2

Hi Julia,

Sorry for the delay in reply.

We’ve tried to recreate your simplified scenario to see if what you are after can be done simply by MAQL.

But I am afraid that complicated calculation like this will have to be done on database level, i.e. by creating a new fact that would tell you number of active users every minute.

Best regards,

Boris from GoodData Support