Loading

Using an Aggregate Field to Define the Level of Detail in a Table Calculation

Udgivelsesdato: Mar 1, 2025
Beskrivelse
When partitions in a view are created by an aggregate field, such as a table calculation or an IF statement, and another table calculation needs to return different results for each partition, then the aggregate field does not appear in the Compute Using options for the table calculation.

For example, if customers fall into one of three categories, "High", "Medium", or "Low", that are defined by the following calculation:

IF SUM([Sales]) < 500
THEN "low"
ELSEIF SUM([Sales]) < 5000
THEN "Medium"
ELSE "High"
END

Then [Customer Name] must be in the view for this bins to be calculated correctly. However, if [Customer Name] is in the view, then the marks in the view are broken up by [Customer Name], which means we will not have one sales total per category.
Løsning

Option 1:


Wrap the aggregate calculation in the level of detail (LOD) expression FIXED. FIXED expressions always return non-aggregate results that can be converted into dimensions. The attached example workbook uses the sample data set Superstore to demonstrate the following directions:

  1. Create a calculated field with a name like "FIXED Sales Category" with a calculation similar to the following:

    { FIXED [Customer Name] : [Sales Categories] }

  2. Replace [Sales Categories] with [FIXED Sales Categories] in the view
  3. Remove [Customer Name] from the marks card

Options 2:


Create a calculation that uses the original logic inside and outside of the table calculation. The attached example workbook uses the sample data set Superstore to demonstrate the following directions:
 

  1. Create a calculated field with a name like "Sales per Sales Category" with a calculation similar to the following:

    IF SUM([Sales]) < 500
    THEN
    WINDOW_SUM(
    IF SUM([Sales]) < 500
    THEN SUM([Sales])
    END
    )
     
    ELSEIF SUM([Sales]) < 5000
    THEN
     
    WINDOW_SUM(
    IF SUM([Sales]) < 5000
    THEN SUM([Sales])
    END
    )
     
    ELSE
     
    WINDOW_SUM(
    IF SUM([Sales]) >= 5000
    THEN SUM([Sales])
    END
    )
    END

  2. Drag [Sales Categories] to the Rows shelf
  3. Drag [Customer Name] to Detail on the marks card
  4. Drag [Sales per Sales Category] to the Columns shelf
  5. Right-click [Sales per Sales Category] and select Compute Using > Customer Name
  6. Ctrl+drag [Sales per Sales Category] from the Columns shelf to Label on the marks card
  7. Navigate to Analysis > Stack Marks > Off.
Yderligere ressourcer
The calculation in option 2 may seem redundant, but every IF statement is necessary. The IF statement inside of the WINDOW_SUM() expression limits the data that is summed up in the WINDOW_SUM(). The WINDOW_SUM() result for any one category will show up in every category. Therefore, we need to place the WINDOW_SUM() expression inside of another IF statement that will only return the result we want in the category we want.


Vidensartikelnummer

001473424

Vedhæftede filer

using aggregation as bins.twbx

1205 KB

 
Indlæser
Salesforce Help | Article