Showing results for 
Search instead for 
Did you mean: 
Employee Alumni
Employee Alumni


This is the second part of the articles that discusses level-based measures.  The essential part of a level based measure is an aggregation that is calculated at a specific level of detail with a result can be repeated at the lower level when they are viewed together.

They can be calculated using the standard SQL window functions.  We are including the SQL logic here using an Incorta materialized view as a comparison.  

If the data has to be persisted, either an Incorta Materialized View or an Incorta Analyzer table can be used.  If the data has to be calculated during runtime because the number shown on a dashboard may be subject to dashboard prompt filters, an Incorta dashboard insight using an Aggregate Table can be used.

See: Level-based aggregate in Incorta (using Analyzer) 


SQL Syntax:

SELECT Column_Name,
       count(Column_Name_ID) OVER (PARTITION BY Column_Name) AS lvl1_row_count,
       min(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE)) OVER (PARTITION BY Column_Name) AS lvl1_discount_min,
       max(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE)) OVER (PARTITION BY Column_Name) AS lvl1_discount_max,
       avg(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE)) OVER (PARTITION BY Column_Name) AS lvl1_discount_avg,
       percentile_approx(((Column_Name_LIST_PRICE-Column_Name_SALE_PRICE)/Column_Name_LIST_PRICE), 0.5, 100) OVER (PARTITION BY Column_Name) AS lvl1_discount_median
FROM Schema_Name.Table_Name


Best Practices Index
Best Practices

Just here to browse knowledge? This might help!

Version history
Last update:
‎03-20-2023 04:28 PM
Updated by: