Inbuilt Aggregation Functions

In ViewZen Analytics, the following inbuilt aggregation functions are supported

Aggregation
Description

Count

It counts the total number of non-null values in a specified column or dataset. For example, in a sales dataset, a count of sales transactions would give the total number of sales recorded.

Distinct Count

A distinct count only includes unique values. For instance, when counting customers, a distinct count will ensure that each customer is only counted once, regardless of how many transactions they have made.

Sum

Calculates the total of all values in a specified numeric field or column. In a sales dataset, summing the "Sale Amount" column will give the total revenue generated across all transactions.

Average

Computes the mean of all values in a dataset by dividing the sum of all values by the number of values in the numeric field. In a student dataset, the average age of all students can be calculated by summing the ages and dividing by the total number of students.

Minimum

Finds the smallest value in a dataset or a given set of values. In a product inventory dataset, the minimum value in the "Stock Quantity" column will show the product with the least inventory.

Maximum

Identifies the largest value in a dataset or a given set of values. In a sales report, the maximum value in the "Sale Amount" column will indicate the highest transaction amount.

Running Total

Keeps a continuous sum of data values over a period or category. It adds each successive value to the sum of all previous values, creating a growing total. In a sales dataset, a running total of sales would show the accumulated sales value month by month, allowing you to track how total sales increase over time.

Difference Sum

Calculates the difference between two values over a specific period or category, usually to analyze change or growth. It subtracts the previous value from the current value to show the change between them. If the sales in January were $10,000 and February were $12,000, the difference sum would show a $2,000 increase between the two months.

Cumulative Count

Tracks the total number of occurrences or entries over time or across categories, incrementing by one for each new event or record. In a customer acquisition report, a cumulative count would show the total number of customers acquired up until each point in time, showing growth over the months.

Cumulative Distinct Count

It counts the cumulative number of unique values in a dataset, considering each distinct occurrence only once, even if it appears multiple times across the period or categories.

  • Imagine a dataset tracking customer purchases. If three customers make purchases over three days:

    • Day 1: Customer A

    • Day 2: Customer B

    • Day 3: Customer A (purchased again)

  • The Cumulative Distinct Count will count 2 unique customers by Day 3 (Customer A and Customer B), even though Customer A made multiple purchases.

Difference Average

The Difference Average calculates the difference between the average values of two data sets, periods, or categories. If the average sales in January were $100 and in February were $150, the difference average would be $150 - $100 = $50.

Difference Minimum

The Difference Minimum calculates the change between the minimum values of two data sets, periods, or categories. If the minimum stock in January was 10 units and in February was 8 units, the difference minimum would be 8 - 10 = -2, indicating a decrease.

Difference Maximum

The Difference Maximum calculates the change between the maximum values of two data sets, periods, or categories. If the maximum sales in January were $1,000 and in February were $1,200, the difference maximum would be $1,200 - $1,000 = $200.

Difference Count

The Difference Count calculates the difference in the number of records or occurrences between two datasets, periods, or categories. If there were 200 transactions in January and 250 transactions in February, the difference count would be 250 - 200 = 50, indicating an increase in transactions.

Difference Distinct Count

The Difference Distinct Count calculates the change in the number of unique values (distinct entries) between two datasets, periods, or categories. If there were 150 distinct customers in January and 180 distinct customers in February, the difference distinct count would be 180 - 150 = 30, showing an increase in unique customers.

Lead Count

The Lead Count function counts the number of occurrences or records in a future period or ahead in time, comparing them to the current data. If there are 200 transactions in January, and you apply a lead count to compare with February, you might get a value of 250, showing that 50 more transactions are expected in the future.

Lead Distinct Count

The Lead Distinct Count function counts the number of unique values or occurrences in a future period or category, ignoring duplicates. If there were 150 distinct customers in January and the lead distinct count for February is 180, it indicates an increase of 30 unique customers in the future.

Lead Sum

The Lead Sum function calculates the sum of values in a future period or ahead in time, allowing you to predict or analyze the total for upcoming periods. If the total sales in January were $10,000, applying the lead sum function to February might show a forecasted total sales value of $12,000.

Lead Average

The Lead Average function calculates the average of values in a future period, helping you estimate future trends or performance. If the average sales in January were $500, applying lead average to February might give an estimate of $600 in average sales.

Lead Minimum

The Lead Minimum function identifies the minimum value in a future period, highlighting the lowest data point expected in that period. If the minimum stock in January was 10 units, applying lead minimum to February might indicate that the minimum stock level is forecasted to be 8 units.

Lead Maximum

The Lead Maximum function identifies the maximum value in a future period or ahead in time, showing the highest data point for comparison. If the maximum sales in January were $1,000, using the lead maximum function for February might show a forecasted maximum of $1,200.

Lag Count

The Lag Count function counts the number of occurrences or records in a previous period or data point. If there were 250 transactions in February, and the lag count for January is 200, the lag count shows that 50 fewer transactions occurred in January.

Lag Distinct Count

The Lag Distinct Count function counts the number of unique values (distinct entries) in a previous period or category, ignoring duplicates. If there were 180 distinct customers in February and the lag distinct count for January is 150, the lag distinct count shows that 30 fewer unique customers were recorded in January.

Lag Sum

The Lag Sum function calculates the sum of values from a previous period or point in time. If the total sales in February were $15,000, and the lag sum for January was $12,000, the lag sum indicates that sales were $3,000 lower in January.

Lag Average

The Lag Average function calculates the average value from a previous period or data point. If the average sales in February were $700, and the lag average for January was $600, the lag average shows that average sales have increased by $100.

Lag Minimum

The Lag Minimum function identifies the minimum value from a previous period or data point, helping you compare the lowest value from the past with the current period. If the minimum stock in February was 5 units, and the lag minimum for January was 8 units, the lag minimim shows that the minimum stock level decreased by 3 units.

Lag Maximum

The Lag Maximum function identifies the maximum value from a previous period or data point, helping you compare the highest value from the past with the current period. If the maximum sales in February were $2,000, and the lag maximum for January was $1,800, the lag maximum shows that the maximum sales value increased by $200.

Cumulative Lead Count

The Cumulative Lead Count calculates the running total or cumulative count of records, occurrences, or events from the current data point forward in time (future periods).

If there are 100 transactions in January, 120 in February, and 150 in March, the Cumulative Lead Count would show:

  • January: 100

  • February: 100 + 120 = 220

  • March: 220 + 150 = 370 This provides a running total of the transactions over time.

Cumulative Lead Sum

The Cumulative Lead Sum calculates the running total of values (such as sales, revenue, or any numeric metric) from the current period forward.

If the sales for January were $1,000, February $1,200, and March $1,500, the Cumulative Lead Sum would show:

  • January: $1,000

  • February: $1,000 + $1,200 = $2,200

  • March: $2,200 + $1,500 = $3,700 This gives a cumulative total of sales over time.

Cumulative Lag Count

The Cumulative Lag Count calculates the running total or cumulative count of records, occurrences, or events from the current data point backward in time (previous periods).

If there are 150 transactions in March, 120 in February, and 100 in January, the Cumulative Lag Count would show:

  • March: 150

  • February: 150 + 120 = 270

  • January: 270 + 100 = 370 This shows the cumulative count of transactions from the past periods up to the current period.

Cumulative Lag Sum

The Cumulative Lag Sum calculates the running total of values (such as revenue, expenses, etc.) from the current period backward in time.

If the sales for March were $1,500, February $1,200, and January $1,000, the Cumulative Lag Sum would show:

  • March: $1,500

  • February: $1,500 + $1,200 = $2,700

  • January: $2,700 + $1,000 = $3,700 This gives the cumulative total of sales from the past periods up to the current period.

Cumulative Sum Percentage

The Cumulative Sum Percentage calculates the running percentage of the cumulative sum in relation to the total sum of a metric over all periods. It shows the percentage contribution of each value to the total sum as it accumulates over time.

Let's say you have sales data for three months:

  • January: $1,000

  • February: $1,500

  • March: $2,500

Total Sales = $1,000 + $1,500 + $2,500 = $5,000

The Cumulative Sum Percentage will show the percentage accumulation of each month’s sales to the total sales:

  • January: ($1,000 / $5,000) * 100 = 20%

  • February: ($2,500 / $5,000) * 100 = 50%

  • March: ($5,000 / $5,000) * 100 = 100%

This gives a clear picture of how each period contributes to the overall total.

Cumulative Count Percentage

The Cumulative Count Percentage calculates the running percentage of the cumulative count in relation to the total count of records or occurrences over all periods. It tracks the proportion of occurrences (e.g., transactions, customers, or events) as they accumulate over time.

Suppose you have transaction counts for three months:

  • January: 200 transactions

  • February: 300 transactions

  • March: 500 transactions

Total Transactions = 200 + 300 + 500 = 1,000

The Cumulative Count Percentage will show the percentage accumulation of each month’s transaction count to the total:

  • January: (200 / 1,000) * 100 = 20%

  • February: (500 / 1,000) * 100 = 50%

  • March: (1,000 / 1,000) * 100 = 100%

This helps you understand how the transaction volume grows as a percentage of the total number of transactions.

Cumulative Count Distinct Percentage

Cumulative Count Distinct Percentage computes the percentage of unique records or entities accumulated up to each period (e.g., month, quarter, or year), relative to the total number of distinct records in the dataset.

Let’s assume you have distinct customer data for three months, and you want to calculate the Cumulative Count Distinct Percentage:

  • January: 100 distinct customers

  • February: 150 distinct customers

  • March: 200 distinct customers

Total Distinct Customers = 100 (Jan) + 150 (Feb) + 200 (Mar) = 450 distinct customers.

Now, we calculate the Cumulative Count Distinct Percentage for each month:

  • January: (100 / 450) * 100 = 22.22%

  • February: (250 / 450) * 100 = 55.56% (Cumulative total for January + February)

  • March: (450 / 450) * 100 = 100% (Cumulative total for January, February, and March)

Last updated