Расчет показателя с истории времен на ДАТУ (конец месяца, квартала, года)

[#1]

Коллеги, добрый день.

Столкнулись с такой задачей:

Необходим расчет показателей НА ДАТУ – не за период «Дата С – Дата ПО», а на отчетную дату с «истории времен» : на конец месяца (на 31.01.2014), квартала (на 31.03.2014), года (на 31.12.2013), конкретную дату в календаре (на Сегодня).

В прилагаемом примере:

первый столбец – это ID убытка, следующие 2 столбца – это Дата заявления убытка (DATE1) и дата закрытия убытка (DATE2)

Ячейка E1 – конец отчетного периода, например: 01.06.2012 это значит считаем показатели на конец мая 2012 г.

В ячейке E2 формула MS Excel, которая дает нужный результат (как просили нас на встрече)

Со столбца G и далее – собственно табличка из Клика, в которой мы не можем сделать такую же формулу, как в Экселе, для расчета Newly registered, nb и Outstanding claims, nb

Заранее спасибо.

[Eugeny_Ilyin]

Добрый день, может будет проще предварительно рассчитать показатели?

[#3]

Евгений, спасибо, такой вариант нами рассматривался, но бизнес его "завернул" как очень трудоемкий. У нас данные за несколько лет, я так понимаю, плоская табличка со всеми предрассчитанными показателями будет довольно объемной.

[Eugeny_Ilyin]

Бегло ознакомился с табличкой. Я так понимаю, требуется подсчет количества ИД убытков в работе на каждый период? Новых, в работе, закрытых?
Может флаги на этапе загрузки исходных данных формировать....
Расчет показателей по периодам трудоемок, но это же разовая операция. Рассчитали и в хранилище положили, и так каждый месяц.

[#5]

Спасибо, этот вариант понятен.

Но в идеале хотелось бы решить вопрос с помощью Set Analisys.

[qlikviewaito]

Может вам подойдет вариант с дополнительным календарем.

Для квартала: одной дате(31.03.2014) будет соответствовать массив дат с 01.01.2014 по 31.03.2014 и т.д.

[#7]

Добрый день.

Я ошибаюсь или Set Analysis не решит поставленную задачу?

Предрасчет кажется более оптимальным и вероятным решением.

[Eugeny_Ilyin]

Расчет остатков в формулах с применением расширенной аналитики возможен, в сообществе встречались примеры.
НО! Формулу нельзя назвать простой и, нагрузка на процессор возрастает довольно сильно.
А если измерений много, да еще с условиями - трудоемкость поиска и сопровождения идеального решения на Set Analisys превысит затраты по варианту с предварительным расчетом.

[vadimtsushko]

Тоже думаю, что Set Analysis тут не поможет.

Я думаю это один из вариантов распространенной задачи - например получение складских остатков на произвольную дату на основе данных по складским движениям.

Два основных подхода это:

- Расчет и загрузка в модель показателей на каждый день.

- Использование таблицы AsOfDate  (типа варианта с дополнительным календарем, который уже предложила @natalia siglina)

Минус первого подхода - объем данных может оказаться слишком большим. Допустим нам нужно хранить остатки в разрезе SKU и складов по каждому дню. Например  50 т. SKU, остатки на 20 складах, данные за 3 года - уже получается миллиард записей.

Но из вашей это пока никак не вытекает - если нужно иметь только суммарное количество Outstanding claims, и closed claims в целом, то это получается всего одна запись на день - спокойно можно рассчитать и хранить. (Как если бы нам на каждый день надо было иметь общую сумму по остаткам в штуках и в сумме по всей номенклатуре и по всем складам)

Если же вам на самом деле на каждый день необходим более детальный анализ - во вложении пример использования второго подхода. Получается что-то вроде вашей таблицы в Excel. (И там же таблица по дням)

Полезный документ о работе с подобным календарем можно посмотреть тут Calendar with flags making set analysis so very simple

!

[Sergey_Polekhin]

Проявлю долю занудства, изменив формулировку на "Set Analysis помогает всегда, но на модели, предназначенной для решения конкретной задачи" и предложу  ряд дополнительных пояснений к уже сформулированным коллегами выше.

1. Два первых расчетных показателя (“Newly registered, nb” и “Newly registered, nb (FULL accum)”) могут быть без проблем рассчитаны средствами только Set Analysis, т.к.:
   • Для первого из них можно четко задать критерии отбора подмножеств (по аналогии с формулами в таблице Excel);
   • Второй показатель, по сути,  является значениями первого с полным накоплением;
   • Но что самое важное, результаты агрегации для обоих показателей как считаются, так и могут быть отображены в виде таблицы в рамках одного и того же используемого измерения (в данном случае Год-Месяц).
2. С третьим показателем сложнее:
   • Его ключевое отличие от первых двух заключается в том, что результаты его отображения по заданному измерению (Год-Месяц) не должны совпадать с расчетным измерением, т.к. расчетное измерение должно включать в себя несколько предшествующих периодов.
   • Очевидно, что Excel благодаря своей простоте использования приучает пользователя мыслить не таблицами, а наборами ячеек, в которых результат отображается на основе произвольно рассчитанных формул, размещенных в произвольных ячейках. Визуально результат пользователем воспринимается как таблица, но принципы ее расчета не являются теми, которые заложены в функционал объектов QlikView «Прямая таблица» или «Сводная таблица».
   • И если бы не стояла задача отобразить все расчеты в единой таблице с фиксированным измерением, то и этот показатель можно было бы рассчитать средствами Set Analysis и отобразить, например, в виде «Текстового объекта». Т.е. по сути, проблема не в Set Analysis, а в выбранном заказчиком табличном способе отображения. Но если форма представления согласована заказчиком, то очевидно, приходится подстраивать модель, что и предложил Вадим.
3. Суть идеи расчета и отображения третьего показателя в QlikView в виде «Прямой таблицы» заключается в том, чтобы разделить две задачи:
   • Позволить строить «Прямую таблицу» по требуемым измерениям.
   • Позволить выполнять расчет значений в ячейках таблицы путем отбора любых требуемых значений средствами Set Analysis вне зависимости от ограничений, задаваемых измерением самой таблицы.
   • Прилагаю свой вариант приложения с подробными комментариями.