I’ve got a dataset which (simplifying slightly) shows an account balance at the end of each month, that I plot as a bar chart, aggregating by date(month) so that you can see how the balance varies over time.
Because the aggregation is over a date column, quicksight adds “drill up to quarter” automatically, which as a feature is great - the finance people like to look at quarters & months. However the value that is displayed when you drill up is the sum of the values for the months in the quarter, which doesn’t make any sense for this type of data.
Is there a way to get quicksight to use the last value for each time period (ie the last balance at the end of the quarter) ? I played around with the lastValue table calculation function, but could only get it to display the overall last value (repeated for each quarter) which isn’t very useful
As I read through the problem statement it appears as an aggregate function (sum) is used plot the chart and as user navigates the date hierarchy, aggregate function reflects the respective values at each level. however what needs to happen is if user is navigating levels up to say quarter now a different aggregate function need to be applied …so is the ask here is to apply different aggregate function at each level of the hierarchy?
You also mentioned that you were able to implement the last function but without the desired outcome, are you able to share a small subset of your data to understand the data grain.
Hi, that’s exactly right.
I have data that looks like this
Ie the balance at the end of december was 40000, at the end of august 10000. & which is plotted either as a bar chart or line chart with date as the x-axis, Currently if i drill up to quarter then the data point for Q4 2021 would be 25000+35000+29000 = 89000 whereas I would like it to be the value at the end of A4, ie 29000
If i understand correctly, your requirement is related more towards showing specific data based on time granularity.
I can think of 2 possible methods (no drill up/drill down within the chart) :
Method 1 :
Create calculated fields to : extract ( Quarter ) , get the last value for each time period (q_chart )
Calculated field : q_chart
extract(“MM”,date) = 3 , balance,
extract(“MM”,date) = 6 , balance,
extract(“MM”,date) = 9 , balance,
extract(“MM”,date) = 12 , balance,
Create 2 charts : Chart 1 for Quarter , Chart 2 for Monthly
Control filtering using actions.
Actions (Select Q4)
Method 2 : Dynamic selection to show specific values based on parameter https://democentral.learnquicksight.online/#Analysis-TipsAndTricks-Calculation-Switch-Date-Aggregation
- Create a parameter with static value (month) and add to control ( values : month , quarter )
- Calculated field (Datefield) =
- Calculated field (Value) =
Thanks for those ideas. It’s a shame there’s no way to make the native drill up/down work.
Hi Fred. I’ll communicate this ask to the correct team. Thanks!