Hello I have some stock market data with fields such as date, ticker, price, and changepercent. This data lives at a daily granularity and I’m trying to calculate various metric over periods of time using visuals that are just not tables.
I was hoping there would be an easy way to calculate a cumulative sum of changepercent by year and partitioned by ticker, but it’s currently more challenging than I thought. With that visual I’m just trying to put it into a simple bar chart (vertical or horizontal).
I also want to do a more complex visual which is a heatmap by year (rows) and month (columns) performing a cumulative sum for each year / month combo, also partitioned by ticker. I’m only looking at 1 ticker at a time, not multiple, but obviously it still needs to be partitioned by ticker. Any idea how I might go about doing some of this.
I tried different variations of runningSum but it appears I need to include all fields within that running sum if I want create a visual with it. This tends to be the error message I see in my visuals VISUAL_CALC_REFERENCE_MISSING.
Another way to go about doing this would be if I could get the change between the min date price and max date price for a selected period of time, but not sure how to go about doing this.
This returns the same results as just summing all the change percent unfortunately. Return is a little more annoying to calculate and there isn’t really any supported functions for CAGR that are not just a singe aggregation. I believe what I need to do is preprocess either via SQL or calculated fields the max start and end for a given time period focus such as weekly, monthly, quarterly, yearly…etc. Then join the values for min and max data so they are side by side and just do simple arithmetic to get the return. At which point I should be able to just sum the values. I’m going to leave this question opened. I imagine someone has done this before, maybe they will see this an provide and answer.
Your intuition is right. When things start to get too complex in the UI falling back to processing the data in the datasource and providing the values needed to visualise the data is a good option.
Please consider adding some sample data (paste in csv format) along with expected results to improve your chances of getting another response.
If we don’t hear back from you in couple of days, I will mark response from @eperts as the solution to this.
. If it doesn’t let me know and I can take another stub at it.