I am trying to find a way how to work with time in workspace. I have timestamps in my source data, which I would like to use in the analytics e.g. to measure how long an event lasted, how long events of some type lasted on average, show number of events on timeline etc. I have not found a way how to do that. Could anybody advice?
Best answer by Martin Burian
It is very good question. If you have timestamps in your source data, it is possible to use it in analytics, but usually it means some preparations in ETL.
By timestamp I understand Date and Time or Epoch time. You cannot directly use it for slicing by hour > minute > second, you have to prepare the time dimension:
It will allow you to slice by the time, but think about if you need second granularity. If not you can just extract hour and add it directly into the fact dataset:
How to calculate the duration? You have 2 options, calculate it in your ETL and have duration as fact or you can add start time epoch and end time epoch as facts and then use just subtract start from end and you will get duration in seconds. Then you can calculate averages etc.
Couple of examples of reports:
Result of the calculation is seconds, so how come there are minutes and seconds. It is because of the time metric format template which is possible to choose in format. It looks as follows:
Clever isn't it?
Do not forget that there is the MAQL which allows you to create metrics exactly according your needs.
Last but not least there is one more case and it is that you can have start and end in different records in dataset. Imagine for example some logs. You have start and end of the event in different records. Model could looks as follows:
How to calculate duration and average now? MAQL!
To sum it up, there is no direct support of time dimension in GoodData as there is for dates, but flexibility GoodData provides allow you to create your own time dimension. You can use epochs as facts and calculate duration of events. Duration is also possible to calculate in ETL, if you do not have it in your data.
Hope it helped.