Расчет остатков на конкретную дату

[romantrishin]

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

Есть основная таблица в которой содержится 2 типа данных - остаток на конец месяца (OnHandEoM) и приход+расход на каждый день (InventTrans).

Есть таблица с календарем в которой содержится для каждой даты информация о принадлежности каждой конкретной даты к типу периода.

Например, для 3 июля есть 1 запись с типом OnHand_SoM Для определения начала месяца

Дата;_ДатаКлюч

3.07.17;30.06.17

И есть 3 записи с типом OnHand_Sum

Дата;_ДатаКлюч

3.07.17;1.07.17

3.07.17;2.07.17

3.07.17;3.07.17

Соответственно имеем формулу для расчета остатков на конкретную дату:

Sum({<ТипПериода={'OnHand_SoM'}, ТипДокумента={'OnHandEoM'}>} Количество) + Sum({<ТипПериода={'OnHand_Sum'}, ТипДокумента={'InventTrans'}>} Количество)

Первая сумма ищет сумму по всем остаткам на конец месяца, вторая сумму всех движений с начала месяца по указанную в измерении дату.

Данная формула работает если в измерении указывать месяц или дату, но если выбрать например неделю, то в период OnHand_SoM попадают 2 записи, если неделя содержит данные из двух разных месяцев.

Можно как-то в set expression определить значение минимальной даты начальных остатков?

Отсюда же следует второй вопрос, например, в таблице есть данные по промо акциям, есть дата начала промо акции и дата ее окончания, в каждой строке они разные, можно как-то определить в формуле эту дату для расчета остатков на начало и конец промо акции?

[vkhassanov]

Расчет остатков на конец дня лучше перенести на сторону скрипта - это статичная информация, нет смысла ее динамически пересчитывать при каждом клике пользователя. Как результат и формулы можно будет существенно упростить.

[romantrishin]

Остатки на каждый день если хранить предрасчитанными - это 20 млрд записей, не вариант.

[Eugeny_Ilyin]

Да, остатки......

Золотую середину найти не так то просто .

Если считать скриптом - теряем место на диске и рискуем получить модель сжирающую память.

Если считать остатки на лету, то с памятью все ОК, но каждое новое состояние будет постоянно пересчитываться и грузить процессор под 99%.

Из собственного опыта - на малых данных - расчет на лету, на больших - расчет в скрипте.

[romantrishin]

Остатков рассчитанных на конец месяца в скрипте хватает, но с проблемой попадания конечных остатков более чем от одного месяца в случае группировки по неделям или кварталам так решить и не удалось. Есть решение не очень хорошее - в календаре промаркировать последние дни месяца, если на этой неделе месяц закончится, аналогично с кварталом. Но в данном случае возникает ситуация если, например, данные выбираются за 2 первых месяца квартала, тогда в расчет не попадет ни один конечный остаток.

Т.е. способа подкинуть в сет анализ дату окончания подпериода (недели/квартала) не существует? Msx(Дата) возвращает максимум по всей выборке, а не по конкретной строке таблицы.

[leonid_burlakov]

Добрый день! Сам пользуюсь As of Date методом для расчёта остатков на любую дату. Пример во вложении. Есть ряд трудностей, например при расчёте оборачиваемости, но в целом метод удобный и работает достаточно шустро.

[romantrishin]

Ну по сути он у меня и используется, только есть еще контрольная точка на начало каждого месяца. Без этой КТ нереально большой объем данных получается. На любую дату он работает, не работает если нужно свернуть до недели, например, когда попадает две контрольных точки, если неделя лежит на границе месяцев.

[leonid_burlakov]

Можете прикрепить образец вашей таблицы? Метод As Of достаточно быстрый и нет необходимости использовать рассчитанные остатки на конец месяц. У меня было 10 тыс номенклатурных позиций и по каждой раз в квартал отдельная партия закупки, плюс перемещения, продажи и всё работало. Конечно, у вас может намного больше записей. В любом случае, легче когда есть исходная таблица. 

[romantrishin]

Как лучше сделать выборку данных? 1 товар в 1 тороговой точке за длительный период? У меня 100 торговых точек * 8000 номенклатурных единиц в среднем за месяц * 5 лет. Только свернутых до дня и типа движения данных на 7-8 миллионов записей в месяц. В итоге со всеми типами строк в основной таблице почти пол миллиарда записей.

[leonid_burlakov]

Пол миллиарда - это получается регистр движения товаров. Да. давайте по 1 товару и 1 торг точке. Конечно объём реально большой, чем смогу подскажу.