Блог

Есть ли способ использовать функцию Over и Intersect, чтобы получить средние продажи за первые 3 периода (не всегда последовательные месяцы, иногда месяц пропускается) для каждого сотрудника?

Например:

• Значение EmpID 1 равно 71.67 ((80 60 75)/3) несмотря на пропуск «3/1/2007»
• EmpID 3 равен 250 ((350 250 150)/3).
• Я не уверен, как будет работать EmpID 2, потому что есть только две точки данных.

Я использовал обходной путь по вычисляемому столбцу, используя DenseRank по дате, «asc», EmpID, а затем использовал другой вычисляемый логический столбец, где имя столбца DenseRank <= 3, затем использовал функции над столбцом Boolean = TRUE, но я хочу понять, как это правильно сделать.

Существуют функции с последним ‘n’ периодом, но я не видел ничего похожего на функцию с первым ‘n’ периодом.

 EmpID    Date   Sales
1   1/1/2007    80
1   2/1/2007    60
1   4/1/2007    75
1   5/1/2007    30
1   9/1/2007    100
2   2/1/2007    200
2   3/1/2007    100
3   12/1/2006   350
3   1/1/2007    250
3   3/1/2007    150
3   4/1/2007    275
3   8/1/2007    375
3   9/1/2007    475
3   10/1/2007   300
3   12/1/2007   200
 

Я полагаю, решение зависит от того, где вы хотите, чтобы эти данные были представлены, но вот один пример

 If((Rank([Date],"asc",[EmpID])<=3) and (Max(Rank([Date],"asc",[EmpID])) OVER ([EmpID])>=3),Avg([Sales]) over ([EmpID]))
 

Вы можете вставить это как вычисляемый столбец, и он даст вам то, что вы хотите (при условии, что ваши данные отсортированы по дате при импорте).

Возможно, вам захочется просмотреть нумерацию строк, и в этом случае вставьте это также как вычисляемый столбец и назовите его RN

Rank([Date],"asc",[EmpID])

Объяснение

Rank([Date],"asc",[EmpID])

Эта часть функции в основном применяет номер строки (помеченный как RN в приведенных ниже результатах) к каждой EmpID группировке.

Rank([Date],"asc",[EmpID])<=3

Вот как мы берем верхние 3 строки, независимо от того, пропущены ли месяцы. Если ваши данные не отсортированы, нам придется создать один дополнительный вычисляемый столбец, но применяется та же логика.

(Max(Rank([Date],"asc",[EmpID])) OVER ([EmpID])>=3)

Это то, что мы в основном игнорируем EmpID = 2 , или любой EmpID , у кого нет хотя бы 3 строк. Удаление этого даст вам среднее значение (динамически) для каждого EmpID из них на основе их первых 1, 2 или 3 месяцев соответственно.

Avg([Sales]) over ([EmpID])

Теперь, когда наши данные ограничены нужными нам строками, просто возьмите среднее значение для каждой EmpID из них.

@Chris — Вот решение, которое я придумал

Шаг 1: Вставил вычисляемый столбец ‘rank’ с приведенным ниже выражением

 DenseRank([Date],"asc",[EmpID])
 

Шаг 2. Создал визуализацию перекрестной таблицы из таблицы данных и ограниченных данных с помощью приведенного ниже выражения