Running total, in spotfire analysis.

[Matt McNally]

In spotfire analysis we would like a running total, to keep track of usage.

Please see Wanted column below:

 

 

 

Date

Time

Feature

IN/OUT

User

Number of tokens

Machine

DateTime

TokensOut

Wanted

 

 

31/12/2019

10:42:50

prog

OUT:

user1

31

machine1

31/12/2019 10:42

31

31

 

 

31/12/2019

11:11:54

prog

IN:

user1

31

machine2

31/12/2019 11:11

-31

0

 

 

31/12/2019

11:14:15

prog

OUT:

user1

31

machine1

31/12/2019 11:14

31

31

 

 

31/12/2019

11:14:51

prog

OUT:

user1

31

machine1

31/12/2019 11:14

31

62

 

 

31/12/2019

11:15:11

prog

OUT:

user1

31

machine2

31/12/2019 11:15

31

93

 

 

31/12/2019

11:48:27

prog

IN:

user1

31

machine1

31/12/2019 11:48

-31

62

 

 

31/12/2019

11:48:45

prog

OUT:

user1

31

machine1

31/12/2019 11:48

31

93

 

 

31/12/2019

11:55:58

prog

OUT:

user2

22

machine2

31/12/2019 11:55

22

115

 

 

31/12/2019

12:09:42

prog

IN:

user1

31

machine1

31/12/2019 12:09

-31

84

 

 

31/12/2019

12:09:56

prog

OUT:

user1

31

machine1

31/12/2019 12:09

31

115

 

 

 

 

We have tried a calculated column:

Sum([TokensOut]) over (Intersect([Feature],AllPrevious([Date])))

But it is not working as intended.

Any assistance is appreciated.

Thanks

[Fabian Duerr]

Your approach is almost correct.

But first you have to make sure that you use the right format for your date and datetime columns. It should be mm/dd/yyyy but you have dd/mm/yyyy. Therefore Spotfire does not treat it as date and datetime.

After fixing this you have to use the DateTime (not the date) column in your calculation

Sum([TokensOut]) over (Intersect([Feature],AllPrevious([DateTime])))

There you go:

 

The results differa bit since some DateTimes are identical. So you might want to correct your DateTime columnas well. It is not identical to Date and Time column. You are missing the seconds.

[Matt McNally]

Great thanks, too many columns named the same thing. Thanks for pointing it out.