Работа с кубиком OLAP в Excel; определять месячные значения в более широком диапазоне дат?

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

В кубе установлены фильтры даты, определяющие месяц и год (они становятся более конкретными, но более тонкие уровни не используются).

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

Я хотел бы создать рассчитанную меру, которая будет отображать средний балл для каждого проекта, если будут выполнены два критерия:

  1. Для всего диапазона дат существует не менее 100 наблюдений
  2. нет месяцев с нулевыми наблюдениями в диапазоне дат, указанном в переменной.

Я также должен уточнить, что диапазоны дат, которые я использую, будут отличаться и длиться и не всегда будут заканчиваться самым последним месяцем, но они будут всегда в течение нескольких месяцев.

Я участвую в этом, так как рассчитанная мера, основанная на этом, обеспечит меня средним только в том случае, если для диапазона дат достаточно наблюдений:

IIF([Measures].[OBSERVATIONS]>=100,[Measures].[AVERAGE],"^^") 

Теперь мне нужно добавить критерии без месяцев с нулевыми наблюдениями.

Я попытался использовать COUNT (), но в этой форме он игнорирует диапазон дат, заданный в pivottable, и возвращает подсчет всех месяцев, когда есть какое-либо значение для проекта, включая нули.

 COUNT(([Calendar].[Calendar].[Month],[Measures].[OBSERVATIONS]),EXCLUDEEMPTY) 

Я попытался определить наименьшее количество наблюдений за месяц, используя это выражение, но опять же игнорирует диапазон дат и не отражает пустые ячейки:

 MIN([Calendar].[Calendar].[Month],[Measures].[OBSERVATIONS]) 

Я думаю, что CurrentMember – это то, что мне нужно включить, но я не могу заставить его работать на меня.

Предполагая, что это релевантно, и я не уверен, что лучший способ объяснить иерархию календаря, так что это отражает то, что у меня есть:

Календарь кубов OLAP

Первый список календаря – это тот, который используется для фильтрации данных сводной таблицы, а также вытягивается в выражения mdx выше.

РЕДАКТИРОВАТЬ:

Спасибо @SouravA за ответ. Я пробовал несколько вещей, и учитывая, что форматирование в комментариях ограничено, вот краткое изложение того, что я сделал.

Я получаю сообщение об ошибке «Query (1,35) Parser: Синтаксис« WITH »неверен»

чтобы убедиться, что я правильно использую это:

После внесения изменений ниже я вложил все это в окно MDX: от инструмента «рассчитанные меры» в Excel.

Я, конечно, изменил НАБЛЮДЕНИЯ и СРЕДИ к именам переменных в моем кубе.

Я изменил '[Project]. [ProjectCode]' на '[Project]. [ProjectName]', как мой куб настроен.

В '[NewMeasure]' я изменил его на имя, которое я использую для рассчитанной меры.

на '[ваш куб]' Я пробовал пару разных вещей; ссылка на куб, которую я использую в формулах куба в excel, выглядит так: «Cubename NormativeCube», поэтому я попытался вставить это в скобки с кавычками и без них, оставив часть NormativeCube и выполняя все это без скобок.

Я также изменил последнюю строку после «WHERE», чтобы отразить диапазоны дат следующим образом: «[Календарь]. [Календарь]. [Месяц]. & 1 & [2015]: [Календарь]. [Календарь]. [Месяц]. & [12] & [2015]

Я также установил это как «Календарь». [Месяц]. [Месяц]. & 1 : [Календарь]. [Месяц]. [Месяц]. & [12] '

Кроме того, еще один вопрос; будет ли эта работа работать в любом диапазоне дат, или она предназначена для указания в MDX? Мне нужно, чтобы он функционировал на основе диапазона дат, заданного фильтрами куба.

EDIT 2:

Мне просто нужно было настроить эту настройку, изменив значение «> 0» на «= 0», поскольку исходное решение показало только среднее значение для тех, у кого не было данных за каждый месяц в диапазоне дат, и отрегулируйте один из календарей установить выражения или что угодно.

 IIF ( [Measures].[OBSERVATIONS]>=100 AND COUNT ( FILTER ( EXISTS ( [Calendar].[Calendar].[Month].MEMBERS ,EXISTING [Calendar].[Calendar].[Month].MEMBERS ) ,[Measures].[OBSERVATIONS] = 0 ) ) = 0 ,[Measures].[AVERAGE] ,"^^" ) 

ИЗМЕНИТЬ 3:

Найдено ограничение;

эта мера работает только в том случае, если диапазон дат, определенный для куба, находится в целом приращении иерархии календаря; т.е. один месяц, один квартал или один год. делать 2 месяца, 2 четверти, 2 года или разбить через 2 четверти или годы, вернет ложный результат из выражения IIF ().

Я играл с несколькими разными способами, чтобы настроить его, но не могу заставить его работать.

EDIT 4:

Re: Календарная иерархия

Внизу [календарь]. [Calendar] есть 4 варианта: Год, Квартал, Месяц и Ключ даты.

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

Свойства элемента в этих трех списках только на следующем уровне вверх по иерархии

В поле «Ключ даты» свойства элемента следующие: Название месяца Месяц года Время Calcs (это мало чем мне известно) Неделя Недели

EDIT 5:

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

 IIF ( [Measures].[OBSERVATIONS]>=100 AND COUNT ( FILTER ( EXISTS ( [Calendar].[Calendar].[Month].Members ,EXISTING [Calendar].[Calendar].Members ) ,[Measures].[OBSERVATIONS] = 0 ) ) > 0 ,[Measures].[AVERAGE] ,"^^" ) 

Использование предложения EXISTING полезно в тех случаях, когда вы хотите, чтобы ваш расчет распознавал определенный выбор (контекст). Следующий код является самоочевидным. Дайте мне знать, если это сработает.

 WITH SET ZeroObservationMonths AS FILTER ( EXISTS ( [Calendar].[Calendar].[Month].MEMBERS ,EXISTING [Calendar].[Calendar].[Date].MEMBERS ) ,[Measures].[OBSERVATIONS] = 0 ) MEMBER Measures.NewMeasure AS IIF ( [Measures].[OBSERVATIONS]>=100 AND COUNT(ZeroObservationMonths) > 0 ,[Measures].[AVERAGE] ,"^^" ) SELECT [Project].[ProjectCode].MEMBERS ON 1, Measures.[NewMeasure] ON 0 FROM [YourCube] WHERE ({[Calendar].[Calendar].[Date].&D1: [Calendar].[Calendar].[Date].&D2}) 

EDIT: Если вы планируете создавать меру внутри excel, просто укажите ниже код MDX в текстовом поле «Новая расчетная мера»,

 IIF ( [Measures].[OBSERVATIONS]>=100 AND COUNT ( FILTER ( EXISTS ( [Calendar].[Calendar].[Month].MEMBERS ,EXISTING [Calendar].[Calendar].[Date].MEMBERS ) ,[Measures].[OBSERVATIONS] = 0 ) ) > 0 ,[Measures].[AVERAGE] ,"^^" ) 

EDIT 2: Если фильтрация может произойти по любому атрибуту, а не только по датам, замените EXISTING [Calendar].[Calendar].[Date].MEMBERS с EXISTING [Calendar].[Calendar].MEMBERS в сценарии выше.

  • Проблема SSAS (2008R2) и фильтра Excel при обновлении данных
  • Как я могу запросить данные в кубе OLAP (без использования Excel)
  • SSAS - структура хранилища данных и неизвестное значение
  • Запись пользовательского драйвера для Excel в OLAP-связь в .Net
  • Соединение SSAS Cube в Excel со встроенной безопасностью будет обновляться только после нажатия кнопки «Включить контент»
  • OLAP-куб, управляющий Excel Pivot. Какое лучшее решение?
  • Публикация данных с листа Writeback excel получает зависания, пока куб обрабатывается
  • Сводные таблицы - VBA
  • VBA - OLAP Cube Filter - проверка наличия элементов
  • Графический цвет графика Excel 2010 без VBA-соединения с кубом (SSAS)
  • Как я могу обновить отдельные ячейки в отчете формулы куба?
  • Interesting Posts
    Давайте будем гением компьютера.