# \#level-aware **URL:** https://community.amazonquick.com/tag/level-aware/159.md [Latest](https://community.amazonquick.com/latest.md) · [Categories](https://community.amazonquick.com/categories.md) · [Tags](https://community.amazonquick.com/tags.md) --- ## [How to handle semi-additive measures (account balance / snapshot) when collapsing hierarchy in a Pivot Table?](https://community.amazonquick.com/t/how-to-handle-semi-additive-measures-account-balance-snapshot-when-collapsing-hierarchy-in-a-pivot-table/53594)
**Author:** [@Robert\_Esteves](https://community.amazonquick.com/u/Robert_Esteves)\ **Replies:** 4\ **Last updated:** [October 1, 2026, 3:56pm UTC](https://community.amazonquick.com/t/how-to-handle-semi-additive-measures-account-balance-snapshot-when-collapsing-hierarchy-in-a-pivot-table/53594 "2026-10-01T15:56:21Z")
Hi everyone, I am building an account balance report in Amazon QuickSight and running into an issue handling a semi-additive measure when collapsing a pivot table hierarchy. Setup & Business Requirement Visual type: P… --- ## [Create a Range Filter with Min/Max Values Defaulted to Pivot Value Fields?](https://community.amazonquick.com/t/create-a-range-filter-with-min-max-values-defaulted-to-pivot-value-fields/51644)
**Author:** [@C\_B](https://community.amazonquick.com/u/C_B)\ **Replies:** 1\ **Last updated:** [February 25, 2026, 10:34pm UTC](https://community.amazonquick.com/t/create-a-range-filter-with-min-max-values-defaulted-to-pivot-value-fields/51644 "2026-02-25T22:34:27Z")
How can I create a range filter (min and max) on a Pivot Table’s value field that defaults to the relevant min/max values in that field in the table? I am trying to replicate a slider control from Tableau (see below), bu… --- ## [How to show string in pivot table cell?](https://community.amazonquick.com/t/how-to-show-string-in-pivot-table-cell/50632)
**Author:** [@cewu](https://community.amazonquick.com/u/cewu)\ **Replies:** 3\ **Last updated:** [December 17, 2025, 4:22pm UTC](https://community.amazonquick.com/t/how-to-show-string-in-pivot-table-cell/50632 "2025-12-17T16:22:51Z")
Hi, I am trying to use pivot table to show my data. For example: my data in dataset like: I want result like: So I created last\_value calculation field using lastValue function: lastValue(value, \[{value} ASC\], … --- ## [How to calculate the maximum daily sum across different granularities in QuickSight?](https://community.amazonquick.com/t/how-to-calculate-the-maximum-daily-sum-across-different-granularities-in-quicksight/50669)
**Author:** [@helseslo](https://community.amazonquick.com/u/helseslo)\ **Replies:** 4\ **Last updated:** [November 27, 2025, 10:29am UTC](https://community.amazonquick.com/t/how-to-calculate-the-maximum-daily-sum-across-different-granularities-in-quicksight/50669 "2025-11-27T10:29:01Z")
Hi! :slight\_smile: I’m looking for a way to display the maximum daily sum on a visual, but for different granularities. My dataset looks like this: Date stock code 01.01.2025 4 A 01.01.2025 4 … --- ## [Need help calculating percentage with fixed denominator across selected filter](https://community.amazonquick.com/t/need-help-calculating-percentage-with-fixed-denominator-across-selected-filter/45790)
**Author:** [@Nitya\_Goriparthi](https://community.amazonquick.com/u/Nitya_Goriparthi)\ **Replies:** 4\ **Last updated:** [November 26, 2025, 4:43pm UTC](https://community.amazonquick.com/t/need-help-calculating-percentage-with-fixed-denominator-across-selected-filter/45790 "2025-11-26T16:43:35Z")
I’m working on a vertical stacked bar chart in Quick Sight where I want to show the percentage of exceptions out of the total. Here’s how I have my calculations set up: Exception Count: sum({IDs}) Total (denominator)… --- ## [Sum while only taking distinct IDs into account](https://community.amazonquick.com/t/sum-while-only-taking-distinct-ids-into-account/49752)
**Author:** [@Caro](https://community.amazonquick.com/u/Caro)\ **Replies:** 3\ **Last updated:** [October 10, 2025, 7:33pm UTC](https://community.amazonquick.com/t/sum-while-only-taking-distinct-ids-into-account/49752 "2025-10-10T19:33:22Z")
We have mutliple Orders, each with multiple ArticleIDs of which each Article has a Time. We want to create the sum of Times for each Order and display the average over all Orders (per day). For the example table this… --- ## [Looking for Static Aggregated Value](https://community.amazonquick.com/t/looking-for-static-aggregated-value/48579)
**Author:** [@cesdom](https://community.amazonquick.com/u/cesdom)\ **Replies:** 10\ **Last updated:** [August 26, 2025, 8:28pm UTC](https://community.amazonquick.com/t/looking-for-static-aggregated-value/48579 "2025-08-26T20:28:49Z")
Hi community! I was trying to create a calculations for one of my tables for multiple days, and one of them is a LAC-A that helps me aggregate my shipments. But when I try to put in in a table adding a new field, my … --- ## [\[Feature request + workaround\] Mode function](https://community.amazonquick.com/t/feature-request-workaround-mode-function/47532)
**Author:** [@B\_Burgess](https://community.amazonquick.com/u/B_Burgess)\ **Replies:** 3\ **Last updated:** [July 7, 2025, 11:47pm UTC](https://community.amazonquick.com/t/feature-request-workaround-mode-function/47532 "2025-07-07T23:47:36Z")
Hello. This post is a feature request for a mode (most common value) aggregation function. I spent the last several hours working on the solution I will present below, but I could have saved a lot of time if this functio… --- ## [Help, help with calculated fields for date comparison](https://community.amazonquick.com/t/help-help-with-calculated-fields-for-date-comparison/47077)
**Author:** [@July](https://community.amazonquick.com/u/July)\ **Replies:** 3\ **Last updated:** [June 9, 2025, 2:20pm UTC](https://community.amazonquick.com/t/help-help-with-calculated-fields-for-date-comparison/47077 "2025-06-09T14:20:47Z")
Hi guys, I must created some calculated fields to compare datas. Could you help me, please? I thank you so much. Create calculated fields in Quick Sight to compute: The “current date” is not the system current date, … --- ## [Mismatch agrregation error](https://community.amazonquick.com/t/mismatch-agrregation-error/43706)
**Author:** [@pantelis](https://community.amazonquick.com/u/pantelis)\ **Replies:** 3\ **Last updated:** [March 28, 2025, 6:59pm UTC](https://community.amazonquick.com/t/mismatch-agrregation-error/43706 "2025-03-28T18:59:05Z")
Hi, Why the below gives me the mismatch aggregation error? I want to find the average amount per Call ID max({Approved Amount USD)},\[{Expense ID}\]) / distinct\_count({Call ID}) --- ## [Trailing 3 Month Totals not showing](https://community.amazonquick.com/t/trailing-3-month-totals-not-showing/44075)
**Author:** [@Forcxe](https://community.amazonquick.com/u/Forcxe)\ **Replies:** 1\ **Last updated:** [March 27, 2025, 7:37pm UTC](https://community.amazonquick.com/t/trailing-3-month-totals-not-showing/44075 "2025-03-27T19:37:30Z")
Hi, I have a pivot table where I want to calculate the Trailing 3 Month overall ctr (clicks / deliveries). The calculations work correctly in my dimension fields (tier), but not appearing at all for totals. I’ve tried u… --- ## [LAC Calculation Help](https://community.amazonquick.com/t/lac-calculation-help/41509)
**Author:** [@cesdom](https://community.amazonquick.com/u/cesdom)\ **Replies:** 6\ **Last updated:** [February 11, 2025, 1:57pm UTC](https://community.amazonquick.com/t/lac-calculation-help/41509 "2025-02-11T13:57:31Z")
Hi! I want to ask if there is a way to get this result. I need to get for each ID the maximum value contained in a week of this operation: shipments / forecast. My information is organized as this image describes. For t… --- ## [標準偏差をグラフで表示したい](https://community.amazonquick.com/t/topic/40187)
**Author:** [@kawasakitr222](https://community.amazonquick.com/u/kawasakitr222)\ **Replies:** 5\ **Last updated:** [December 23, 2024, 3:10am UTC](https://community.amazonquick.com/t/topic/40187 "2024-12-23T03:10:41Z")
以下のように車両の走行ごとの各ギアポジションでの走行距離と燃料消費量のテーブルがあります。 このテーブルから車両ID別・ギアポジション別の燃費の平均と標準偏差を求めるために 計算フィールドを作成作成しました。平均と標準偏差を棒グラフにしたいのですがLAC集計式のためグラフ化できません。 事前にSQLで車両IDとギアポジションで集計することで回避できそうですが、分析で走行IDの走行距離でフィルターをかけたいため、事前集計は避けたいで… --- ## [LACs share grouping key and do not work anyways](https://community.amazonquick.com/t/lacs-share-grouping-key-and-do-not-work-anyways/39008)
**Author:** [@Filo](https://community.amazonquick.com/u/Filo)\ **Replies:** 5\ **Last updated:** [December 6, 2024, 5:00pm UTC](https://community.amazonquick.com/t/lacs-share-grouping-key-and-do-not-work-anyways/39008 "2024-12-06T17:00:41Z")
Hello Team! I was trying to create LAC calculation using dynamic grouping key. To make it super simple, I created the following example: Where Category or product is a column driven by parameter (two options, Categ… --- ## [How to get maxOver calculation with timestamp precision up to seconds in Quick Sight Direct Query?](https://community.amazonquick.com/t/how-to-get-maxover-calculation-with-timestamp-precision-up-to-seconds-in-quick-sight-direct-query/38064)
**Author:** [@InfernoRider1](https://community.amazonquick.com/u/InfernoRider1)\ **Replies:** 4\ **Last updated:** [December 6, 2024, 3:54pm UTC](https://community.amazonquick.com/t/how-to-get-maxover-calculation-with-timestamp-precision-up-to-seconds-in-quick-sight-direct-query/38064 "2024-12-06T15:54:37Z")
Hi Quick Sight Community, I’m working on a level-aware calculation (LAC) in Quick Sight to get the maximum timestamp for a field grouped by timeRange. The field contains timestamps updating each second, but when I use m… --- ## [LAC for column averages](https://community.amazonquick.com/t/lac-for-column-averages/38016)
**Author:** [@jemin](https://community.amazonquick.com/u/jemin)\ **Replies:** 13\ **Last updated:** [November 25, 2024, 10:36pm UTC](https://community.amazonquick.com/t/lac-for-column-averages/38016 "2024-11-25T22:36:09Z")
Hi, I have two views, first view has topics % values customers customers\_all A 31.53% 19.27M 61.1M B 2.37% 1.3M 54.38M C … … … D … … … % values are calculated as sum(customers) / sum(custo… --- ## [Daily average distinctcount (LAC?, AvgOver?)](https://community.amazonquick.com/t/daily-average-distinctcount-lac-avgover/36801)
**Author:** [@Lucas1](https://community.amazonquick.com/u/Lucas1)\ **Replies:** 4\ **Last updated:** [November 6, 2024, 12:32am UTC](https://community.amazonquick.com/t/daily-average-distinctcount-lac-avgover/36801 "2024-11-06T00:32:48Z")
Here is the translation of your text into English: “Good evening, I need my data to show the daily average of this indicator, the counts are distinctcountif. In the case of the image, for example, the final result shoul… --- ## [LAC calculation on a visual](https://community.amazonquick.com/t/lac-calculation-on-a-visual/36767)
**Author:** [@mamilone](https://community.amazonquick.com/u/mamilone)\ **Replies:** 3\ **Last updated:** [October 14, 2024, 2:51pm UTC](https://community.amazonquick.com/t/lac-calculation-on-a-visual/36767 "2024-10-14T14:51:48Z")
I have created 2 LAC calculated fields, d\_bucket: count({outb\_final\_quantity\_requested},\[{prod\_product\_id},{site\_site\_id},{order\_date}\])/ distinct\_count({LAG String},\[{prod\_product\_id},{site\_site\_id},{order\_date}\]) d\_… --- ## [集計された数値におけるヒストグラムを作成したい](https://community.amazonquick.com/t/topic/33932)
**Author:** [@trswkz](https://community.amazonquick.com/u/trswkz)\ **Replies:** 1\ **Last updated:** [August 18, 2024, 3:44am UTC](https://community.amazonquick.com/t/topic/33932 "2024-08-18T03:44:19Z")
ECサイト内の全購入データの一覧をデータセットとして持っているときに "ユーザごとの購入金額の分布"をヒストグラムで表示したいと考えています。 | ユーザID | 購入金額 | 購入日時 | といったものを想定していただいて大丈夫です。 その際に、SQLでいうところの "SELECT ユーザID, SUM(購入金額) FROM TABLE GROUP BY ユーザID"のようにユーザ毎の購入金額の合計を算出し、そちらの分布を表… --- ## [Problem with a LAC calc](https://community.amazonquick.com/t/problem-with-a-lac-calc/32684)
**Author:** [@gprice](https://community.amazonquick.com/u/gprice)\ **Replies:** 7\ **Last updated:** [August 5, 2024, 2:41pm UTC](https://community.amazonquick.com/t/problem-with-a-lac-calc/32684 "2024-08-05T14:41:54Z")
I’m trying to get some LAC calculations to work but I’m hitting a wall. I have a viz that has a date-range filter and displays metrics of employees. One metric shows the % of Total Sales for each employee. I also have … --- ## [Level-Aware Calculations: Tips and Best Practices](https://community.amazonquick.com/t/level-aware-calculations-tips-and-best-practices/17936)
**Author:** [@David\_Wong](https://community.amazonquick.com/u/David_Wong)\ **Replies:** 7\ **Last updated:** [July 30, 2024, 3:40pm UTC](https://community.amazonquick.com/t/level-aware-calculations-tips-and-best-practices/17936 "2024-07-30T15:40:02Z")
Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced… --- ## [ユーザーごとに週の和を求めたい](https://community.amazonquick.com/t/topic/31992)
**Author:** [@optarc.yamaji](https://community.amazonquick.com/u/optarc.yamaji)\ **Replies:** 2\ **Last updated:** [July 1, 2024, 7:13am UTC](https://community.amazonquick.com/t/topic/31992 "2024-07-01T07:13:45Z")
こちらでご相談させていただいた件です。 以前ご回答くださった方法で、通常は正しく計算ができていたのですが、計算がうまくいかないときが出てきました。 状況としては、以前提示したデータイメージでお伝えした通り、複数itemに該当する週が今週だった場合に集計の数字が5ではなく8とか10になってしまいます。 自分でも考えてみたのですが、うまくいかずで、解決策をご教授いただいたいです。 --- ## [Using Custom Aggregation String Field as a Dimension](https://community.amazonquick.com/t/using-custom-aggregation-string-field-as-a-dimension/30598)
**Author:** [@AnthonyN](https://community.amazonquick.com/u/AnthonyN)\ **Replies:** 4\ **Last updated:** [June 7, 2024, 2:30am UTC](https://community.amazonquick.com/t/using-custom-aggregation-string-field-as-a-dimension/30598 "2024-06-07T02:30:18Z")
Hi Everyone, Reposting as my previous post was locked over the holiday weekend. Thanks for the response @DylanM and I hope you had a good holiday weekend. Your answer made more sense regarding why using that calculated … --- ## [LAC / Aggregating Strings in tables (find top/bottom columns)](https://community.amazonquick.com/t/lac-aggregating-strings-in-tables-find-top-bottom-columns/29601)
**Author:** [@Filo](https://community.amazonquick.com/u/Filo)\ **Replies:** 7\ **Last updated:** [June 1, 2024, 4:04am UTC](https://community.amazonquick.com/t/lac-aggregating-strings-in-tables-find-top-bottom-columns/29601 "2024-06-01T04:04:12Z")
Hello Team! I have the following dataset: I need to create one column named “Top shop” that will display the name of the top shop per region and “Bottom shop” that will find the bottom shop. This table should look l… --- ## [Creating a link between JIRA Epics/Stories](https://community.amazonquick.com/t/creating-a-link-between-jira-epics-stories/30491)
**Author:** [@AnthonyN](https://community.amazonquick.com/u/AnthonyN)\ **Replies:** 1\ **Last updated:** [May 24, 2024, 7:31pm UTC](https://community.amazonquick.com/t/creating-a-link-between-jira-epics-stories/30491 "2024-05-24T19:31:26Z")
Hi everyone, I’m trying to create a dashboard that is able to use the ‘epic link field’ in our JIRA data to associate stories with their respective EPIC. The idea being that I can derive a value from the main EPIC to al… --- ## [PRE\_FILTER not working on sumOver(distinct\_countif())](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873)
**Author:** [@chanQS](https://community.amazonquick.com/u/chanQS)\ **Replies:** 3\ **Last updated:** [May 23, 2024, 4:01pm UTC](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873 "2024-05-23T16:01:52Z")
Im having trouble using the PRE\_FILTER parameter. the calculations are below. calc1 sumOver(distinct\_countIf({customer\_id}, {date} \>= {curr\_month\_start\_date}), \[\], PRE\_FILTER) calcl2 sumOver(sumif({total\_amount}, {da… --- ## [Level Aware calculations at a dimension and date level](https://community.amazonquick.com/t/level-aware-calculations-at-a-dimension-and-date-level/27182)
**Author:** [@srujanaa](https://community.amazonquick.com/u/srujanaa)\ **Replies:** 4\ **Last updated:** [May 3, 2024, 3:40pm UTC](https://community.amazonquick.com/t/level-aware-calculations-at-a-dimension-and-date-level/27182 "2024-05-03T15:40:00Z")
Hi All, Can anyone help me with the calculation for Received value for dimension C from the below table: Thanks, Srujana --- ## [Sum over max values & ifelse problem](https://community.amazonquick.com/t/sum-over-max-values-ifelse-problem/28753)
**Author:** [@dggoumah](https://community.amazonquick.com/u/dggoumah)\ **Replies:** 5\ **Last updated:** [April 29, 2024, 3:21pm UTC](https://community.amazonquick.com/t/sum-over-max-values-ifelse-problem/28753 "2024-04-29T15:21:30Z")
Hi all, im struggling to get a formula created which sums up all max values from a calculated field, get the correct data for a database field which doesnt provide a number but TRUE/FALSE string and get a value out of a … --- ## [Calculating client retention and churn](https://community.amazonquick.com/t/calculating-client-retention-and-churn/28141)
**Author:** [@rohit\_SB](https://community.amazonquick.com/u/rohit_SB)\ **Replies:** 9\ **Last updated:** [April 16, 2024, 6:09am UTC](https://community.amazonquick.com/t/calculating-client-retention-and-churn/28141 "2024-04-16T06:09:45Z")
I’m trying to calculate client retention. I have a few goals: KPI\] one absolute for total # of clients retained \[KPI\] one % for # of clients retained/total # of clients Period window 3a. churn (number of clients at st… --- ## [Aggregation on Table calculation not working](https://community.amazonquick.com/t/aggregation-on-table-calculation-not-working/27604)
**Author:** [@mtu](https://community.amazonquick.com/u/mtu)\ **Replies:** 2\ **Last updated:** [March 25, 2024, 3:40pm UTC](https://community.amazonquick.com/t/aggregation-on-table-calculation-not-working/27604 "2024-03-25T15:40:37Z")
Hi Quick Sight Community! I have a question about the level-aware calculation/table calculation that I’ve been stuck for a while and need your help. here’s the sample of data: Flow--------------------------------Sales… [Next page](https://community.amazonquick.com/tag/level-aware/159.md?match_all_tags=true&page=1&tags%5B%5D=level-aware)