cancel
Showing results for 
Search instead for 
Did you mean: 
suxinji
Employee
Employee
 

Overview

This is the second part of the articles that discuss about level-based measure.  The essential of a level based measure is an aggregation that are calculated at a specific level of details and the result can be repeated at the lower level when they are viewed together.

They can be calculate 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 Incorta Materialized View or Incorta Analyzer table can be used.  If the data has to be calculated during runtime as the number shown on the dashboard may be subject to the dashboard prompt filers, an Incorta dashboard insight using Aggregate Table can be used.

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

Solution

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
suxinji_0-1651701142362.png

 

Version history
Last update:
‎05-14-2022 11:20 AM
Updated by: