Calculating days since the first record in fact table

Jean-Pierre Hoedenaeken

Hello,

We have a fact table with the following records :

We need to calculate the number of days since the first views (# Vues) of an content (Id Contenu) for each record.

So the result will be :

And finally, we can create the following dataviz that shows the consumption evolution :

Is there some functions to achieve that through a "Calculation" ?

Or, due to the large volume of the fact table, is it better to create a materialized view in our datawarehouse ?

Best regards.

Jean-Pierre

Federico Venturin

Hi @Jean-Pierre Hoedenaeken ,

I would create a materialized view in the datawarehouse.

You could calculate it with something like TIMESTAMPDIFF(SQL_TSI_DAY, MIN(Date by Id Contenu), Date), but it requires to have both Date and Id Contenu columns in your analysis/visualization/query to preserve the correct granularity. In Analytics Classic you should be able to display the chart properly (by excluding not required columns), but in DV I believe it won't work..

Gianni Ceresa

Hi Jean-Pierre,

Is that simple datasets in DV or you are doing the work via the RPD?

As you talk about "fact table", I assume there is the RPD (Semantic Model) somewhere. If you add a measure being a MIN() and another being a MAX() on your date, you can then have a derived column being the difference between the 2.

If you will need to create a materialized view depends purely on the performance you get for your analysis and if that's enough for you (consider how often you open that visualization, if caching can be used and how that compare to having a MV that you have to maintain and refresh as part of your data loading process etc.).