Search code examples
spotfire

Aggregation "Sum" over HH:mi:ss data

I have a list of music with several informations including number of listening ( Vues ) and the duration of the music ( Colonne 2 ). I calculated a new column ( Temps écoute ) by multiplying these two.

Then I made a Treemap visualization. My aim was to see how much time I spent listening this Artist, and further, this album.

So I have this :

1

But when I want to aggregate my "Temps écoute" by Sum, I can't :

2

I thought it was a format problem of my value but it's a normal HH:mi:ss :

enter image description here

So my question is first of all, is spotfire able to sum times ? And if yes, how ?

Solution

  • Your 'Temps ecoute' column is of datatype Time so it is not quite a duration, it is like the time portion of a date. To turn it into a duration you can try 

     DateDiff(Time(0,0,0,0),[Temps ecoute])

    This becomes a TimeSpan. You can aggregate a TimeSpan with Sum, or go via an intermediate function to transform the TimeSpan into a number of minutes (or seconds or hours etc. there are different Total functions available) e.g. 

     TotalMinutes(DateDiff(Time(0,0,0,0),[Temps ecoute]))