I believe Quicksight can do this, but cannot figure it out. Say I have a dataset that has price, date, and ticker, the data currently lives in a long format. From my dashboard I have a date filter which will have a start and end date. So say for example we are looking at data for a single ticker from 2020/01/01 - 2022/01/01 (YYYY/MM/DD).
I would like to grab the first value from the user selected 2020/01/01 and compare it against all other values from 2020/01/02 - 2022/01/01 in order to calculate the change between the two. This is a good way of looking at a visual and saying if I had bought X at 2020/01/01 I would have returned Y if I sold on whatever future dates. It would even be great if it compares against itself and starts at 0.
I believe I need to use some type of parameter to do a lookup against the min date from the user selection which would then fill all rows for that parameter column which I can hopefully just perform simple arithmetic to get the answer.
The only alternative is to have static precalculated return fields for weekly, monthly…etc but this would be so much better, as I’d like to avoid doing a direct query at all costs
In Amazon QuickSight, you can achieve the desired calculation by using a combination of parameters, calculated fields, and table calculations. Here’s a step-by-step approach to calculating the change between the first selected date and subsequent dates for a specific ticker:
Create a parameter to capture the user-selected start date. Let’s name it “Start Date.” Set the data type of the parameter to Date and configure the available values based on your dataset.
Create a calculated field to identify the rows that correspond to the first selected date. Name it “Is First Date.” The formula should compare the date in the dataset with the selected start date parameter.
Is First Date = MIN({date}) OVER (PARTITION BY {ticker} ORDER BY {date}) = ${Start Date}
This formula uses the MIN window function to identify the earliest date for each ticker. It then compares that date with the selected start date parameter. If they match, the calculated field will return true (1), indicating that it’s the first selected date for that ticker.
Create another calculated field to calculate the change between the first selected date and subsequent dates. Name it “Price Change.” The formula should perform the necessary arithmetic calculations.
Price Change = IF(${Is First Date}, 0, {price} - FIRST_VALUE({price}) OVER (PARTITION BY {ticker} ORDER BY {date}))
This formula checks if the current row corresponds to the first selected date for the ticker using the Is First Date calculated field. If it is, the price change is set to 0. Otherwise, it calculates the difference between the current price and the first value of the price for that ticker.
Add the “Start Date” parameter, the “Price Change” calculated field, and any other required fields (such as “Ticker” and “Date”) to your visual.
Configure a table calculation to display the cumulative change over time. In the visual, select the “Price Change” field, click on the “Add Table Calculation” button, and choose “Running Calculation.” Configure the table calculation as needed to display the cumulative change, such as running sum or running average.
By following these steps, you’ll be able to calculate and display the change between the first selected date and subsequent dates for a specific ticker. The use of parameters and calculated fields allows you to perform this calculation within QuickSight without the need for additional direct queries.
@tarushtg This looks very promising, the logic of it is exactly what I’m looking for. However am I using SQL + Quicksight parameters to do this calculation? I did not know I could do min over partition by natively in Quicksight.
Let’s say I’m trying to create this at the analysis level as shown in the screenshot below. How would I go about doing that? Currently I get an error message when I try to create the first calculated field, I’ve changed Start Date to startDate as my parameter. The error I get says, the syntax of the calculated field or expression is incorrect.
If this is SQL + Parameters then I imagine I will need to do it at the dataset level. I would like to avoid SQL as much as possible as this dataset / tables are ~100GB. If that’s the only way to accomplish this though I understand an can try to get more creative about the performance of my database / tables.
Edit: Just for some more context. More specifically I want to avoid anything that uses direct query. All of my data is stored as Spice datasets, due to the fact that I have datasets that are getting closer and closer to the enterprise cap on size. Also as mentioned above by database is slow and costly and don’t want to incur additional costs with slow results using direct queries. I think I’m thinking about it the wrong way though. If I create a parameter in a Spice dataset it won’t have to go and query against my database it will just query against the Spice dataset and should ideally give much better performance than a direct query would. As at least part of the results would be preprocessed on whatever the default is for my startDate parameter. I’m going to go back and adjust my Query as I think the solution you mentioned above is definitely at the dataset level and mixing SQL + Parameters.
@Koushik_Muthanna@tarushtg Yeah using minOver is a start I can get the min data (startDate parameter), but now I need to do one of two things. I need another calculated column that fills up all values (price) for that startDate parameter or I need to be able to do a lookup against that startDate parameter. For instance if I could use the percentDifference function and instead of passing an int as the lookup index I could pass my startDate parameter and compare against it that way.
Another maybe possible thing to do would to be calculate the number of periods (days) difference between startDate and all periods (dates) after that start date. Then I could maybe pass that int difference as the lookup index to percentDifference…?
User can then change the start date filter on the dashboard and see performance from that start date going forward. The chart and the example data are not related… different ticker, but should get the gist of it.