Quicksight distinct count over. To achieve this you need to add Calculated Field to an Analysis ( as described here ). Quicksight distinct count over

 
 To achieve this you need to add Calculated Field to an Analysis ( as described here )Quicksight distinct count over distinct_count 関数は、ディメンションまたはメジャーの個別の値の個数を計算し、選択した 1 つ以上のディメンションに基づいてグループ化して返します。

We introduced the QuickSight order of evaluation, and walked through three use cases for LAC-A static keys and two use cases of LAC-A dynamic keys. I have 2 questions: is the User Count 7-Day Avg actually “total distinct user count for last 7 days” here’s the formula calculated field: The formula contains the word windowAvg, but it’s always a lot higher than distinct total user count per each day - so it can’t be the average? how to calculate distinct user count for rolling 30 days? (and plot. Or, if you want to aggregate via QuickSight, you would use sumOver (sum (Ans_Count), [ID]). Analytics formatting per type in QuickSight. Before you create your first analysis, make sure to complete the steps in Setting up for. windowCount would allow you to specify a ‘lookback’ window of how many data points prior (or after) you want to count. Misalnya,distinct_countIf(product type)mengembalikan jumlah total jenis produk unik dikelompokkan berdasarkan dimensi (opsional) yang dipilih, tanpa. The total stations below are static numbers which can not be grouped by months. You might encounter screenshots or procedural text that doesn't reflect the new look in the QuickSight console. Once we add ability to get total number of distinct users, you should be able to do everything in QuickSight. Get the distinct customers with their salary, add a dummy column called “flag”. The correct total should be 12,660. You might encounter screenshots or procedural text that doesn't reflect the new look in the QuickSight console. Li_Yang June 21, 2023, 3:38am 1. I have data that looks like this: Employee Employment Age 1 a 35 1 b 35 2 a 40 3 a 35 4 a 51 4 b 51 I want to calculate the average age of the individual employees. I want to filter this column of distinct count to anything more than 1. xlsx sample data source and create a new dataset. 0. AWS QuickSight: Calculate Aggregate Percentage in Table. With these two fields selected, QuickSight shows the year to date sales of the latest. 1 Answer. order by ID; drop table Ejemplo; You can see what I put in order the field 'ID', because the function peek () depend about that. Essentially how many people had at least one session per week in the first month of their registration. I have added the total stations in the dimension bucket and use the function distinctCountOver({master station}, [{super region},region], P&hellip; Hello, I am trying to do a distinct year-to-date count in quicksight calculations. How would we sum up all distinct count values of an ID over the previous 4 weeks (not including the current week) divided by 4 (number of weeks)?. Quicksight supports the following: A wide range of data sources ( Amazon Athena, Apache Spark, etc). 1- check the count per asin with runningCount (asin, [date ASC], [asin]) in order to have a row count per asin. You can follow the prompts to set up anomaly detection or forecasting based on the data in that visual. countIf ( Revenue, # Conditions CalendarDay >= $ {BasePeriodStartDate} AND CalendarDay <= $ {BasePeriodEndDate} AND SourcingType <> 'Indirect' ) If this column is not numeric, then need to create a new calculated field. I agree with the above suggestions to use. Did you try using function distinct count over? You can follow the same calculation logic, instead of using sum, can use distinct count. If you want another column with the values repeated, use sumOver (Ans_Count, [ID], PRE_AGG). email. Amazon QuickSight Community How to filter on distinct count column in the values fields. ifelse ( month = 5 OR ( month < 3 AND year = 2000 ), 'yes', 'no') then-expression. I succeed creating the calculated field, but when I tried inserting it into the visualization - I got the next error: " Your calculated field expression contains invalid syntax. computing the distinct count of months across all students and showing the same in the Total. As far as for the workaround you might have to perform such computation in data-prep using joins. I am trying to make a calculated field in AWS Quicksight for leads that have closed. , with the help of these charts, we can see the drop in the count of users in each step of. QuickSight dashboards can also be embedded into applications and portals to deliver insights to external stakeholders. The count function calculates the number of values in a dimension or measure, grouped by the chosen dimension or dimensions. View solution in original post. i basically want to not sum duplicate values twice. QuickSight account name. SELECT time, COUNT (*) OVER (ORDER BY time) AS users FROM ( SELECT user, MIN (time) AS time FROM users GROUP BY user ) t. For example, distinct_countIf(product type) returns the total number of unique product types grouped by the (optional) chosen dimension, without any duplicates. Amazon QuickSight uses un-normalized histograms, which use an absolute count of the data points or events in each bin. Not applicable. Date filters. Click yes or no. Our spice dataset is organized by transactions. How to use `countif` in QuickSight. I create one more filter based on “Purchases”, and I select:Jan 2023. Refer to your QuickSight invitation email or contact your QuickSight administrator if you are unsure of your account name. My dataset has over 10 million rows, which is data for 3 months. I’ve tried so many different things, but I still keep getting 8771. AWS Regions provide multiple physically separated and isolated. See here to learn about cross data set filters. percentileDiscOver uses actual values to determine result. Viewed 309 times Part of AWS Collective. Unlike traditional on-premise BI systems, SaaS BI allows businesses to access and utilize robust analytics capabilities through web-based applications, without. Part of AWS Collective. avg({Sales}) You can also specify at what level to group the computation using one or more dimensions in the view or in your dataset. distinct_count 関数は、ディメンションまたはメジャーの個別の値の個数を計算し、選択した 1 つ以上のディメンションに基づいてグループ化して返します。. Union this pre-aggregated data with the original table. (Optional) The time period across which you're computing the computation. answered Jan 20, 2021 at 9:15. x axis i have date range (date field); bars i have active field (sum); The goal is to sum over date range. For example, if three cities share the same ranking, the fourth city is ranked as fourth. For more information on QuickSight's new look, see Introducing new analysis experience on Amazon QuickSight. calculations, howto. By Raj Redshift. We want to be able to clone or copy one dashboard created on one data source to a new data source using the SDK or AWS CLI. 0 Remove duplicate values in googlesheet. x axis i have date range (date field); bars i have active field (sum); The goal is to sum over date range. Connect QuickSight to many different sources. 1. The total stations below are static numbers which can not be grouped by months. We have not heard back from you regarding your question. When a calculated field formula contains an. I am trying to create a dual axis combo chart - one axis showing count per day (date) and the other axis showing cumulative count (over the full chart). Note: this method will not work to find a moving distinct count. A pop-up window appears with the. Add text to add context to sheets in an analysis by using a text box. Thanks for the reply @David_Wong - the calculated field “Ratio” is calculated as follows: “ifelse(${RatioDesired} = ‘Job Level Distribution’, {JOB_LEVEL. For example id. On the analysis page, choose the pivot table visual that you want to edit. the 3 / 5 should refer to the total sum of the rows. ) SELECT version, COUNT (DISTINCT email) AS userCount FROM all_sessions GROUP BY version ) ds. I am trying to use distinct_countif and was wondering if the. I use a simple count distinct : distinct_count({id}) and it’s OK. It can be a field name like address1, a literal value like 'Unknown', or a call to another function. The AWS global infrastructure is built around AWS Regions and Availability Zones. g. Select category. Before you apply these calculations, look at the following brief introduction to partition functions. In an existing line chart, look for an insight notification in the menu on the visual widget. e. Working with data sources in Amazon QuickSight. jjc March 18, 2022, 3:27pm 1. Question & Answer. It seems that distinct count is double counting. You can also generate row number in your ETL (for this example I kept. Active Users (Last 90 Days): ifelse (dateDiff ( {completion_date},now ()) <=90, {user_id},NULL) This calculated field aggregate is set to Count Distinct and it works well! When trying to calculate the number of active users over the total sum of users to return the percent of total during the same 90 day period, this is where I am getting the. However, if you changed the x-axis to let’s say a date then it would take the count of distinct key’s per date and calculate it then. PDF RSS. Dear Community, I want a view of a pivot table but I don’t want to see the count column. The Team view provides a high-level overview of the evaluation forms. Currently, my sumover calc looks something like this and attainment shows 180% but it should be 160% since values for c. However there are multiple closed statuses in use. QuickSight is smart enough to figure out that author wants to aggregate data to the visual level first, and then use the division. Improve this answer. Use the following procedure to join tables to use in a dataset. However, my dataset contains duplicate rows which are created by addition of group and division fields ( many-to-many relationship with owner_id field ). You can try to concatenate values: SELECT offlocation AS city, COUNT (distinct offbegindate || '-' || crscode) as no. g. sumOver ( sum ( {Billed Amount}), [ {Customer Region}] ) The following screenshot shows the results of the example. count 10 product A count 10 product A count 20 product B count 20 product B Create some sample data. . I tried Notion – The all-in-one workspace for your notes, tasks, wikis, and databases. CountThis example shows how to use ARRAY_AGG () to pivot a column of output into an array in a single row: This example shows the use of the DISTINCT keyword with ARRAY_AGG (). distinct_countIf({row_id}, {country} = ‘New Zealand’) / sumOver(distinct_count({row_id})) Hello Tin, Totals and subtotals are blank for table calculations on metrics aggregated by distinct count. Count ( {$<CPRODUS= {'A19'}>} distinct CCLIENT) OR // value changes based on selections. e each person can have 0 to 10 values). Example answer is 3. when an ID has one occurence in May and one in June, it should count it once in May, but not in June - however, as the sum of. 362 3 3 silver badges 10 10 bronze badges. However, if you changed the x-axis to let’s say a date then it would take the count of distinct key’s per date and calculate it then. Once we add ability to get total number of distinct users, you should be able to do everything in QuickSight. date 1. Sorted by: 5. QuickSight delivers fast and responsive query performance by using a robust in-memory engine (SPICE). Now, I’d like to calculate a count distinct in a visual disregarding any dimension of the visual I tried distinctCountOver({id},) and it doesn’t works. But before we get into the Query or QuickSight charts details, let’s understand benefits of using the CUR, what data transfer is, as well as the data transfer types and usage types associated with data transfer. Valid granularities include YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, and SECONDS. 1. Ahmed_She March 30, 2023, 3:22pm 1. The count of queries run depends on the cardinality of the dimension used in the pivot table. To get this use: count ( {<Color= {'RED'}>}DISTINCT ID) The total count of all ID records where Color is RED would be 5 based on the instances of the same two IDs in the role table. An aggregated measure or dimension that you want to see the running count for. Amazon QuickSight is a fast, cloud-native, serverless, business intelligence service that makes it easy to deliver insights to everyone. For Computation type, choose Period over period, and then choose Select. Essentially how many people had at least one session per week in the first month of their registration. This is called a LAC-A function. Amazon QuickSight notifies you on a visual where it detects an anomaly, key drivers, or a forecasting opportunity. Amazon QuickSight Community Distinctcountover not taking calculative field as partition by field. Calculates running Distinct Count of a field, over the specified time period, partitioned by time period and all the group-by dimension. I have managed to get the result expected though not sure that it is the best way to do it (especially if you have some dates when you get sales only for one country): avgOver (sum (sales), [country])/distinct_count (date) Share. Below is the SQL code for achieving this. Upon reading the documentation, it seems distinct_countIf() works for a measure or a dimension, so I concluded it is not possible. Amazon QuickSight at a glance. status-1. Text can hold directions, descriptions, or even hyperlinks to external websites. For example, avgIf (ProdRev,CalendarDay >= $ { BasePeriodStartDate} AND CalendarDay <= $ { BasePeriodEndDate} AND SourcingType <> 'Indirect') returns the average for that. The expression to return if its if argument is evaluated as true. check_in_date <=. These appear as bars on the chart. date, calculations. Agreed, it should be possible in the analysis too. (Optional) The time period across which you're computing the computation. 1. Level-aware calculations are now generally available in all supported QuickSight Regions. @ctine - This is because at the visual level there are only 5 distinct Months available (across all the students). Arguments. We are hoping to add this soon. You can also select Add. . All other period functions are available in these two links: Period over period computation - Amazon QuickSight, Period to date computation - Amazon. minOver ( sum ( {Billed Amount}), [ {Customer Region}] ) The following screenshot shows the results of the example. They also include minOver and maxOver, which can calculate minimum and maximum over partitions. Aggregation -> No Aggregation. (Analyses only) To use an existing parameter, enable Use parameters,. Amazon QuickSight Community Sum of distinct countif. The red highlights show that the partition field with the value " Billing " has three entries, one for each region. If this is no longer relevant after seven days, we will archive the question. distinct_count関数は、指定したカラムの値の内、値に重複がないカラムの総数をグループ化した条件別に算出します。 distinct_countIf関数 使い方としては、avgIf関数と同様にパラメータの設定・条件式を定義します。 1 Answer. Creator II. The following function returns a count of the sales transactions ( Revenue) that meet the conditions, including any duplicates. of unique course offerings FROM offering GROUP BY offlocation ORDER BY COUNT (distinct offbegindate,crscode) as no. Jesse March 28, 2022, 9:55pm 6. When I use count_over function I get results however when I use. It's consumed by an analysis parameter through controls, calculated fields, filters, actions, URLs, titles, and descriptions. The countOver function calculates the count of a dimension or measure partitioned by a list of dimensions. Measures are numeric values that you use for measurement,. 3; asked Jan 24, 2022 at 9:40. QS, today as default. The following tips can help make it easier for Amazon QuickSight to automatically map fields between datasets, for example if you are creating a filter action across datasets: Matching field names – Field names must match exactly. This is because it will count the distinct ComponentId per week or per day. To perform distinct count by date, you would need to use distinctCountOver. filters. Hi, I want to use the function distinctCountOver - counting distinct IDs over a month (and adding this filed to a table that is not aggregated by month). Make sure the dataset you want to use is selected in the drop-down in the dataset section (1) and click the add button in the top left corner (2) Click Add visual and create your visual as you would normally. hoffman November 29, 2022, 2:10pm 2. To get this use:Publishing Your Dashboard. My requirement is that i have to count the distinct phone numbers in January but only the ones that also appeared in the previous month. These fields are indicated in Mode with blue icons. Take care that multiple filters applied to. If you do not have a QuickSight account please follow the instructions in the Initial Setup. It allows you to easily embed rich interactive dashboards to your. ) Amihai. Choose Your subscriptions at left. Use the distinctCountOver function for calculated fields in Amazon QuickSight analyses. Both bar charts and line charts are useful for. You can specify either ascending (. 49 = 29857. for example. e. So if you have an id of 1234 on week 33 and week 43 then it will count it twice on your progress because that is a unique component for that week. Thank you Andy for your help. Distinct count on customer type; Distinct count on country of residency;. Sum of Distinct Count (ID) Question & Answer. If someone can help, it will be great, thank you !. Now, I’d like to calculate a count distinct in a visual disregarding any dimension of the visual I tried distinctCountOver({id},) and it doesn’t works. From the documentation: For LAC-W functions, the visual aggregation defaults to MIN to eliminate duplicates. My dataset is basically a time-series with IDs. Thanks for the reply @David_Wong - the calculated field “Ratio” is calculated as follows: “ifelse(${RatioDesired} = ‘Job Level Distribution’, {JOB_LEVEL. docs. =>. Abs(expression) – Returns the absolute value of a given expression. screenshot of Quicksight using the above instructions. Example. In this image, the product “ChatBot Plugin. We look forward to your feedback and stories on how you apply these calculations for your business needs. The following function returns a count of the sales transactions ( Revenue) that meet the conditions, including any duplicates. Massimo, thanks for the prompt response! I should have stated that the table goes on much longer and contains multiple records with the same ID. Granularity of YEAR means YearToDate computation, Quarter means QuarterToDate, and so on. Thanks, Sri. A simple count can show how many products are sold for each region. Improve this answer. Partition functions. I’d rather just be able to show the count for any existing column I choose. Go to the. The problem comes when calculating the total number of messages, distinct_count(messageId), because in each row of the table, it’s filtered by violation so in every row the divisor is the same as the dividend so the percentage is always 100%. g. Please find my below query. The distinctCountOver function calculates the distinct count of the operand partitioned by the specified attributes at a specified level. mansiish March 10, 2023, 10:47pm 1. com distinctCountOver - Amazon QuickSight. I need a calculation that first get the Age value. Khizar. Viewed 633 times. You can follow the prompts to set up anomaly detection or forecasting based on the data in that visual. Data blocks parameter—if you have over 10GB of data, start with the default compression algorithm and test other compression algorithms. I figured out how to do it via browsing more posts using the AGGR function: sum (aggr (sum (DISTINCT [Sales]),. Using distinctCountOver in the dimension field. Applying one of the workarounds listed in the feedback item reporting this deficiency, your query could be rewritten as:. ty 01/07/2022 10:00 001 01/02/22 09:20 Purchase 50 1. A common ask from Amazon QuickSight administrators is to understand the lineage of a given dashboard (what analysis is it built from, what datasets are used in the analysis, and what data sources do those datasets. You can try to concatenate values: SELECT offlocation AS city, COUNT (distinct offbegindate || '-' || crscode) as no. The blue line is the weekly distinct count for the selected Product and the red line is the weekly distinct count for all products. image 785×265 11. Add a comment | 0 Try -100 instead of -52. So is there any way how do I remove the count column? PFA the view of my analysis. For more information on QuickSight's new look, see Introducing new analysis experience on Amazon QuickSight. rogerpegler. of unique course offerings FROM offering GROUP BY offlocation ORDER BY COUNT (distinct offbegindate,crscode) as no. I would like to find out a distinct count of the ID (in this example, account_id and patient_id are the same), where there was at least one session (each row) in weeks_from_reg 0 AND 1 AND 2 AND 3. (Optional) Modify the visual to meet your requirements (for example, by adding a filter or changing the visual type). eg: we have: name id karthik 1 harsha. One or more measures and dimensions that you want to sort the data by, separated by commas. sum({Sales}) You can also specify at what level to group the computation using one or more dimensions in the view or in your dataset. We are using AWS Quicksight to create dashboards to view the data. However, it will only count it once for the total because there is only one unique Component Id total. Under QuickSight access to AWS services, choose Add or remove. 6 KB I want to know how many clients bought 501+570 togheter in a period (month e. Each of these functions returns values grouped by the chosen dimension or dimensions. version = b. The distinct_count function calculates the number of distinct values in a dimension or measure, grouped by the chosen dimension or dimensions. You can create a join between any of the data sources supported by Amazon QuickSight, except Internet of Things (IoT) data. Each of these functions returns values grouped by the chosen dimension or dimensions. Running distinct count in quicksight. Goal: plot a line chart with. In the menu the opens, click "use parameter". 0. To create a histogram, you use one measure. Parameters are named variables that can transfer a value for use by an action or an object. QuickSight auto-maps fields across data sets by their name and type, and applies this mapping to filter on all mapped fields coming from multiple data sets. If Ticket_No is null no need to count employee. You can either change the aggregation in the field wells, or wrap your. How can I dynamically create a constant with the total number of messages to use it in all the. He loves to code. QuickSight has carefully designed concepts and features that enable analysis builders, such as QuickSight authors, to design content-rich, interactive, and dynamic dashboards to share with dashboard viewers. Pick a line chart on the visualize panel and on the fields well add the year in the color. It might even be as simple as minOver (Ans_Count, [ID], PRE_AGG) and using SUM aggregation on top of it in the visual. Let flag=1. My table has some valued more than once. For example, when you are looking at the number of unique. Count ( {1<CPRODUS= {'A19'}>} distinct CCLIENT) // value remains same regardless of selections. The following example sums Billed Amount over Customer Region. E. I’m not able to get the same value(The. Is there an option to use multiple ifs condition or countifs in QuickSight? I’m trying to select all those ids that meet two conditions in two separate columns. 1. There are two varieties of the percentileOver calculation available in QuickSight: percentileContOver uses linear interpolation to determine result. Numeric filters. Amazon QuickSight at a glance. One or more measures and dimensions that you want to sort the data by, separated by commas. By using parameters, you can create an easier way for a dashboard user to interact with dashboard features in a less technical way. QuickSightを1年ほど使って、簡単に出来そうで出来なかったことをまとめてみました。 正確な1ヶ月の平均値を出す. whenever I tried adding at the moment that I'm configuring the data source, it'd fail. version, b. Distinct Count – Provides a count of how many different values are in the selected measure, for the selected dimension or dimensions. Amazon QuickSight can automatically map fields between datasets in the same analysis. If you want to count all the data points in a given partition I would use countOver instead. The most useful statistic for this metric is SUM, which represents the total number of failed visuals during a set period. lastSessionDate. Example: if you have the columns: date_year (YEAR), date_quarter (QUARTER) and date (MONTH), being date_year = date and. minOver ( sum ( {Billed Amount}), [ {Customer Region}] ) The following screenshot shows the results of the example. dateDiff takes two dates as arguments. Sum of Distinct Count (ID) Question & Answer. Creator II. Amazon QuickSight powers data-driven organizations with unified business intelligence (BI) at hyperscale. Click Add dataset. Is it possible ? As the distinct_countIf function exists, I thought it was possible to mix the Over & If methods in the same time, but it’s not. Hi exeprts, I have a fairly complicated formula I’m trying to accomplish in QuickSight. I’m trying count distinct ID’s but combining different companies into categories and using it as a filter. QuickSight Actions provide advanced filtering capabilities through single point-and-click actions on dashboards. This computes the number of orders per customer, before the display-level aggregation of the visual. In the calculated field editor, enter the name and formula: Profit Ratio = profit / revenue. The date dimension over which you're computer PeriodOverTime calculations. Ideally, this would be drillable too, so a user could drill down to the week or day view but preserve the year-over-year view. Before you begin, import or connect to your data. Usually, you use custom window functions on a time series, where your visual shows a metric and a date field. cant do distinct sum if in quicksight. Active Users (Last 90 Days): ifelse (dateDiff ( {completion_date},now ()) <=90, {user_id},NULL) This calculated field aggregate is set to Count Distinct and it works well! When trying to calculate the number of active users over the total sum of users to return the percent of total during the same 90 day period, this is where I am getting the. I have added the total stations in. Option 1: Use FIXED to find the running count distinct. Though, one thing I was hoping for was to keep my x-axis as a date value rather than simply the month integer. Table 2 = ADDCOLUMNS ( SUMMARIZE ('Table', 'Table' [Column A], 'Table' [Column B], 'Table' [Column C]), "Column D", CALCULATE (COUNTROWS ('Table')) ) Here is the power query for your requirement. For example, 4/26/2020 would become 2, because April is in the second quarter of the year. For more information about LAC-A functions, see LAC-A functions. Note: You want to weigh the cost of the window functions if you are working with a large dataset (rows and columns). total courses: distinctCountOver (Course, [],PRE_FILTER) Calculate CorseCompletions percent by user. To find a feature or item, use the Quick search bar. Say you have a line chart with at a daily level, but you want to count the records from. Using text boxes. The query returns the data in a similar way whether you connect to a database, a software as a service (SaaS) source, or the Amazon QuickSight analytics engine . These perform the same type of aggregation, based on a condition. Joining multiple CSV files. However this would only provide a count of start_date. PDF RSS. countIf ( Revenue, # Conditions CalendarDay >= $. Amazon QuickSight now makes it easier for you to spot the highlights or low-lights in data through conditional formatting. This column (Users) has “count disctinct” setting turned on. 0 AWS Quicksight - question about creating a calculated field using if else and custom aggregation. Hi, I have a parameter to switch between two field - sample size & z-score. I can see here that I can use describe-dashboard or describe-data-set to. So I should simply get “2” in this case. 2 Answers. Question & Answer. With conditional formatting, you can specify customized text or background colors based on field values in the dataset, using solid or gradient colors. ; On the Manage QuickSight page, in the menu at the left,. in your example: A/ingestion/Complete appears twice once for Apple, and once for Banana → and you want to count it as 2 under Complete A/preprocessor/Complete appears 6 times, once for Apple and 5 times for Banana: however, you want to count it as 1 under Complete. Hi Karthik, Max’ suggestion did not really help me for my case (but very handy). Assuming we have a created_date column that we aggregate at the week level (starting on Sundays). Partition functions. (Please refer the attached image). サポートされているレベルは、 PRE_FILTER および PRE_AGG です。. A distinct count can show how many different products are sold for each region. On the Visual types pane, choose the word cloud icon. ABC Customer/12345/100. We will update the data to the current date so your calculations are realistic. When we compare the results with Profit Ratio we created earlier, the numbers are quite different! This is because Profit Ratio calculates the transactional-level ratio first and then finds the average; whereas Profit. This is my data (simplified): Date User First Purchase Type Amount Q. The following example calculates the average sales at the Country level, but not across other dimensions (Region. The fields in the table calculation are in the field wells of the visual. To publish your dashboard, click on Share on the top right-hand corner of the analysis and choose Publish Dashboard: After doing this, you’ll be prompted to share your. How to get the total Accounts . I have approached this. The count (sales) function returns the total number of sales completed. A date field or a call to another function that outputs a date. Related questions. I did it the following way: runningSum (sum ( {count}), [truncDate ("MM", {creationdate}) ASC], [ {region}]) and I found an interesting catch. These perform the same type of aggregation, based on a condition. I would like to count the number of days that users have logged in over a period of time and then group the results by the number of days logged in so I can create a power user curve. occamatic occamatic. The expression must have the same data type as the other then arguments and the else argument.