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
