I am trying to calculate the performance of a single stock as a continuous series in AWS Quicksight, preferably at the analysis level using only calculated fields.
Say for example I have a dashboard with 1 filter which is also a parameter called startDate. My dataset is a long format table of stock prices with the fields date, price, and ticker.
example data:
example desire output:
I'm having a hard time figuring out how to do this using just Quicksight. There is no function which includes a lookup that is not an integer, so I cannot lookup by date. I've tried different versions of min and minOver comparing the startDate parameter vs the date in my dataset. Which does return the correct date if the date exists for that ticker, if the date doesn't exist then it returns 'no data' on the visual side.
Even when I get the correct startDate (the date exists for selected ticker) I cannot really use the output. I need to either fill up all the price values for my startDate parameter, then create another calculated field to do simple arithmetic to calculate performance (new - old / new). I'd rather use some of Quicksights built in functions but when I use functions like percentDifference I get error messages not allowing me to use aggregated fields. Also I can't even lookup for the actual price value for the startDate parameter as there's no function to do so.
Another option might be to calculate the difference as an integer between dates an use that as the index lookup to one of Quicksight's built in functions.
