2

I'm using aws quicksight and would like to create a calculated field of the difference value between my rows (and not in a pivot table) so I can visualize the difference between each measure 

for example i have this data-set:

2019-05-09T10:52:02.000Z   0.45556
2019-05-09T14:52:03.000Z   0.46766
2019-05-09T18:52:03.000Z   0.47887
.
.
.
.

And i would like to add a calculated field that will show:

2019-05-09T10:52:02.000Z   0.45556  0
2019-05-09T14:52:03.000Z   0.46766  0.0121
2019-05-09T18:52:03.000Z   0.47887  0.01121
.
.
.

The main goal is to visualize (line chart) the change per reading i am getting....

Is it possible to do?

Should I create additional data-set with custom SQL query and join them?

Appreciate the help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
shahar zidon
  • 127
  • 2
  • 8

1 Answers1

1

I believe you are looking for the difference function that is available to analysis-level calculated fields:

https://docs.aws.amazon.com/quicksight/latest/user/difference-function.html

So for the dataset:

mytime                     mymetric
------------------------   -------
2019-05-09T10:52:02.000Z   0.45556
2019-05-09T14:52:03.000Z   0.46766
2019-05-09T18:52:03.000Z   0.47887

The definition for your new calculated field would be something like this:

difference(
     sum( {mymetric} ), 
     [{mytime} ASC],
     1
)
JD D
  • 7,398
  • 2
  • 34
  • 53
  • Yes!this function is available only for pivot table which does not allow you to polt it or make a graph out of it :-( – shahar zidon May 19 '19 at 12:09
  • are you sure this is only available for pivot tables? the documentation does not state this and you create a calculated field using `difference` without associated it with any visualization? what errors do you get when you try and put this calculated fields into a visualization? – JD D May 21 '19 at 13:09
  • unfortunately it doesn't work unless you are using a pivot table – shahar zidon May 23 '19 at 03:43
  • 1
    please not that in the aws documents the function difference is under "table calculation function index" https://docs.aws.amazon.com/quicksight/latest/user/difference-function.html – shahar zidon May 23 '19 at 03:44