Подсчет элементов с несколькими критериями

У меня есть таблица (getECRs) в PowerPivot.

Прямо сейчас мне удалось создать вычисляемый столбец, который подсчитывает, сколько раз идентификатор клиента строки (BAN) встречается в столбце BAN по следующей формуле:

=CALCULATE(COUNTROWS(getECRs),ALLEXCEPT(getECRs,getECRs[BAN]))

У меня возникли трудности с добавлением нескольких критериев в формулу CALCULATE в PowerPivot.

В каждой строке есть столбец, в котором указана дата создания запроса _CreateDateKey. Я пытаюсь включить критерии, которые будут включать несколько BAN, только если они попадают в течение 7 дней (до или после) _CreateDateKey для строки.

Например, для одного БАНа есть следующие даты и их ожидаемое количество:

_CreateDateKey    Count    Explanation
6/13/2014         3        Does not include 6/23
6/13/2014         3        Does not include 6/23
6/16/2014         4        Includes all
6/23/2014         2        Does not include the 2 items from 6/13

В Excel я бы использовал оператор COUNTIFS, как показано ниже, чтобы получить желаемый результат (с использованием именования структуры таблицы)

=COUNTIFS([BAN],[@BAN],[_CreateDateKey],">="&[@[_CreateDateKey]]-7,[_CreateDateKey],"<="&[@[_CreateDateKey]]+7)

Но я не могу понять относительные критерии, необходимые для дат. Я попробовал следующее в качестве критерия для функции CALCULATE, но это привело к ошибке:

getECRs[_CreateDateKey]>=[_CreateDateKey]-7

Ошибка: Column '_CreateDateKey' cannot be found or may not be used in this expression.


powerpivot
person guitarthrower    schedule 02.01.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Эта формула отвечает на ваш конкретный вопрос. Это хороший шаблон, чтобы его можно было использовать повторно - EARLIER() ссылается на значение текущей строки (немного сложнее, чем это, но это конечный результат):

=
    CALCULATE (
       COUNTROWS ( getECRs ),
           FILTER (
             getECRs,
             getECRs[BAN] = EARLIER ( getECRs[BAN] )
             && getECRs[_CreateDateKey]
                 >= EARLIER ( getECRs[_CreateDateKey] ) - 7
             && getECRs[_CreateDateKey]
                 <= EARLIER ( getECRs[_CreateDateKey] ) + 7
                   )
               )

По сути, вам, вероятно, следует отказаться от «мышления Excel» об использовании вычисляемого столбца и справиться с этим с помощью меры.

Адаптация вышеизложенного будет выглядеть так: будет использоваться контекст фильтра PIVOT, в котором вы его использовали (например, если BAN был строками, вы получите количество для этого BAN).

Возможно, вам придется настроить ALL(), если он слишком «открыт» для вашего реального контекста, и вам, возможно, придется иметь дело с итоговыми значениями, используя HASONEVALUE():

=
     CALCULATE (
          COUNTROWS ( getECRs ),
          FILTER (
            ALL(getECRs),
                getECRs[_CreateDateKey] >= MAX ( getECRs[_CreateDateKey] ) - 7 &&
                getECRs[_CreateDateKey] <= MAX ( getECRs[_CreateDateKey] ) + 7
                 )
                )
person Jacob    schedule 02.01.2015
comment
Спасибо за ваш ответ. Данные не всегда будут отсортированы (хотя я мог бы настроить свой запрос таким образом). Но даже в этом случае не учитываются 7 дней до или после даты текущей строки. - person guitarthrower; 05.01.2015
comment
Я также полностью согласен с тем, чтобы отказаться от мышления Excel. Результат этой функции будет использоваться в операторе IF для возврата текста, чтобы я мог использовать его в качестве метки строки или столбца в моей сводной таблице. У меня сложилось впечатление, что меры не могут вернуть текст. - person guitarthrower; 05.01.2015
comment
Сортировка не имеет значения. 7-дневное окно должно быть тривиальным, я отредактирую свой ответ позже. Вы абсолютно можете вернуть текст в меру. - person Jacob; 05.01.2015
comment
Спасибо за обновление. Мне нужно многому научиться, когда дело доходит до PowerPivot. Ваша первая функция (для вычисляемого столбца) сделала свое дело. Предложенная вами мера не соответствует ожидаемому (текст или нет). Спасибо за помощь. Это заставляет меня двигаться. - person guitarthrower; 05.01.2015