CODEWITHSUNDEEP
amazon-quicksight
Jay Hyber
Jay Hyber

Reputation: 321

How To Effectively Display Pivot Chart in Quicksight using Datetime as Column

I have a problem displaying Pivot Chart in Quicksight

enter image description here

This is the formula that i used:

difference( max(fuelvolume), [devicedatetime DESC], 1, [{plate_number}] )

Supposedly, October 14 have data but it does not display, How to solve this?

Upvotes: 0

Views: 507

Answers (1)

DataNut
DataNut

Reputation: 317

In order for October 14 to display data, there would need to be a data point on the table for October 13. Is that the case? When it comes to difference formula's the leading or last value will not have a cell entry, depending on if you are sorting by ASC or DESC. In other words, the first or last value will not have an entry because there is nothing to compare it to so that a difference can be found.

Edited to provide the original poster a potential solution in addition to an explanation as to why the problem in the original post is occurring.

This code will place the fuel volume from October 14 in the October 14 cell on the table and still calculate the difference for the subsequent dates. If the October 14 fuel volume is not what should appear in the October 14 cell, the "true" portion of the ifelse formula can be updated to reflect the desired result.

ifelse(isNull(difference( max(fuelvolume), [devicedatetime DESC], 1, [{plate_number}] )),max(fuelvolume),difference( max(fuelvolume), [devicedatetime DESC], 1, [{plate_number}] ))

Here is the result of the difference nested into the ifelse formula. enter image description here

Upvotes: 1

Related Questions