How to display time series totals and stacked column.

[Matt McNally]

Hi,

I've recently started to use Spotfire to try and analyse some usage files.

I have got to the point that I can plot the running total of the licenses using a scatter plot.

But what would be great is to have the total broken down by how much each user is using at a given time.

 

 

 

DateTime

Feature

IN/OUT

User

Number of tokens

Machine

TokensReal

RunningTotal

 

 

31/12/2019 10:51

feature1

OUT:

user1

1

F1DSK23949

1

1

 

 

31/12/2019 10:52

feature1

IN:

user1

1

F1DSK23949

-1

0

 

 

31/12/2019 10:54

feature1

OUT:

user2

1

F1DSK23919

1

1

 

 

31/12/2019 12:42

feature1

IN:

user2

1

F1DSK23919

-1

0

 

 

02/01/2020 10:18

feature1

OUT:

user3

1

F1DSK23459

1

1

 

 

02/01/2020 10:26

feature1

OUT:

user4

1

F1DSK23915

1

2

 

 

02/01/2020 10:32

feature1

IN:

user4

1

F1DSK23915

-1

1

 

 

02/01/2020 10:57

feature1

OUT:

user5

1

F1DSK23917

1

2

 

 

02/01/2020 10:57

feature1

IN:

user5

1

F1DSK23917

-1

1

 

 

02/01/2020 11:13

feature1

OUT:

user2

1

F1DSK23919

1

2

 

 

02/01/2020 12:08

feature1

IN:

user3

1

F1DSK23459

-1

1

 

 

02/01/2020 12:09

feature1

OUT:

user3

1

F1DSK23459

1

2

 

 

02/01/2020 12:11

feature1

IN:

user3

1

F1DSK23459

-1

1

 

 

02/01/2020 12:13

feature1

OUT:

user3

1

F1DSK23459

1

2

 

 

02/01/2020 12:13

feature1

IN:

user2

1

F1DSK23919

-1

1

 

 

02/01/2020 13:28

feature1

IN:

user3

1

F1DSK23459

-1

0

 

 

02/01/2020 13:38

feature1

OUT:

user3

1

F1DSK23459

1

1

 

 

02/01/2020 13:43

feature1

OUT:

user2

1

F1DSK23919

1

2

 

 

02/01/2020 14:47

feature1

OUT:

user5

1

F1DSK23917

1

3

 

 

02/01/2020 15:38

feature1

OUT:

user4

1

F1DSK23915

1

4

 

 

02/01/2020 15:50

feature1

OUT:

user4

1

F1DSK23915

1

5

 

 

02/01/2020 15:58

feature1

IN:

user5

1

F1DSK23917

-1

4

 

 

02/01/2020 15:58

feature1

OUT:

user5

1

F1DSK23917

1

5

 

 

02/01/2020 16:05

feature1

IN:

user2

1

F1DSK23919

-1

4

 

 

02/01/2020 16:10

feature1

IN:

user4

1

F1DSK23915

-1

3

 

 

02/01/2020 16:10

feature1

IN:

user4

1

F1DSK23915

-1

2

 

 

02/01/2020 16:22

feature1

OUT:

user5

1

F1DSK23917

1

3

 

 

02/01/2020 16:26

feature1

IN:

user3

1

F1DSK23459

-1

2

 

 

02/01/2020 16:26

feature1

OUT:

user3

1

F1DSK23459

1

3

 

 

02/01/2020 17:11

feature1

IN:

user5

1

F1DSK23917

-1

2

 

 

02/01/2020 18:23

feature1

IN:

user3

1

F1DSK23459

-1

1

 

 

02/01/2020 18:31

feature1

IN:

user5

1

F1DSK23917

-1

0

 

 

 

 

 

 

Any ideas as to how we would be able to highlight which users are using the license and when

 

Many thanks in advance,

Best regards,

Matt

[Fabian Duerr]

Hi Matt, be careful with the format of your date time column. If you want Spotfire to recognize it as a date time format you should use mm/dd/yyyy instead of dd/mm/yyyy. You can create a new corrected column using:

 

ParseDateTime([DateTime],"dd/MM/yyyy HH:mm")

 

 

 

Instead of using a calculated column for your running total you can then use the Cumulative Sum of TokensReal on the y-axis. Pick this in the aggregation methond or input the following expression. (For this aggregation you x-axis has to be categorical style):

 

Sum([TokensReal]) THEN Sum([Value]) OVER (AllPrevious([Axis.X]))

 

 

 

Finally make some trellis and color by user:

 

 

Please note that your x-axis only contains the dates from your table. It's not a linear axis. If you want something more like a continuous scale then follow this approach:

 

https://community.spotfire.com/questions/running-total-spotfire-analysis

 

Let me know if this was helpful