Уроки по работе с DataLens. Разбираем реальные кейсы. Сборник.

Юлия Стрекалова

Уроки по работе с DataLens. Разбираем реальные кейсы. Сборник.

Как получить порядковый номер дня года в DataLens

Сегодня давайте рассмотрим задачку на первый взгляд непростую для реализации в DataLens. Как нам получить порядковый номер дня года. В типовых функциях DataLens есть возможность быстро получить только номер дня в месяце. Но очень часто возникает потребность узнать именно номер дня в году. Например, 31 декабря – это обычно 365 день в году.

Как же это сделать штатными средствами Даталенс?

На самом деле, сделать это очень просто. В этом на поможет функция DateTrunc. Если помните, данная функция изменяет размерность дат и группирует данные на первое число нужной размерности (на первое число квартала или года). Поэтому с ее помощью сначала давайте определим первое число года для каждой даты.

***

Справка

Возвращает дату, округленную по аргументу unit.

Синтаксис

DATETRUNC( datetime, unit [ , number ] )

Описание

Если задан аргумент number, то происходит округление до этого количества временных единиц. Отсутствие аргумента эквивалентно значению 1.

Поддерживаемые значения unit:

"second";

"minute";

"hour";

"day" (при заданном number используется номер дня в году);

"week";

"month";

"quarter";

"year".

Типы аргументов:

datetime — Дата | Дата и время

unit — Строка

number — Целое число

***

Таким образом, формула будет выглядеть так.

В нашей таблице рядом с датами появился первый день каждого года. Для наглядности добавим новое поле в чарт.

И теперь все, что нам надо, это рассчитать разницу в днях между исходной датой слева и началом года. Например, 1 декабря 2022 минус 01 января 2022.

Но обратите внимание, что к полученному результату нам нужно добавить 1. Так как формула рассчитывает именно разницу, дельту дней, то она не учитывает тот самый первый день года. Его мы и добавляем в конце.

В итоге мы получаем порядковый номер дня в каждом году.

Вот таким несложным способом можно получить в DataLens номер дня в году.

Создаем отчет в DataLens со сроком оплаты.

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

Поэтому сегодня рассмотрим задачу, где дата будет играть ключевую роль. Думаю, что многие знают, что такое сроки оплаты по договору. Эта формулировка частенько встречается в договорах, а менеджеры должны тщательно следить за соблюдением этих сроков во избежание штрафов и простоев.

Задача

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

Решение

Для решения данной задачи нам нужно будет сделать всего лишь один расчет – просто добавить к дате в отчете две недели.

В этом нам поможет функция DateAdd.

***Справка

Данная функция позволяет выполнять с датой необходимые вычисления. Например, рассчитать новую дату, прибавив к ней 5 дней или 5 месяцев.

datetime– исходная дата, из которой нужно получить результат.

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

Аргумент unitпринимает следующие значения:

"year" — год;

"month" — месяц;

"day" — день;

"hour" — час;

"minute" — минута;

"second" — секунда.

number– аргумент, указывающих число дней, месяцев или лет, на которое надо вычислить дату. Задается целым числом. Может принимать отрицательные значения.

***

Согласно синтаксису, наша формула будет выглядеть вот так. Просто указываем исходную дату (дата выставления счета), размерность (неделя) и количество (2). То есть плюс 2 недели.

Если бы нам нужно было убавить две недели назад, то указали бы -2.

И получаем наш результат. Теперь у нас есть еще одна колонка с датой на две недели больше. То есть это будет последняя дата для оплаты счета.

Теперь в отчете сразу видно, когда и по какой поставке истекает срок оплаты.

А теперь давайте рассчитаем, сколько у нас осталось дней для оплаты, и подсветим цветом истекающие сроки оплаты. Предположим, что текущая дата – это 12.12.2022. Добавим для наглядности этот столбец.

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

Сделать это можно с помощью функции TODAY().

Справка

Данная функция очень похожа на NOW(). Но она возвращает лишь текущую дату, без времени.

А разница в днях вычисляется простым вычитанием. Вот так:

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

Для этого надо зайти в настройки нового поля.

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

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

Как разбить массив на строки в DataLens

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

И, предположим, есть задача разделить этот массив на строки, чтобы значения распределились по строкам. Вот так:

Как функция ALL_CONCAT(), только наоборот.

С этой задачей нам поможет функция UNNEST(). Просто помещаем в нее наш массив, переносим новое поле в таблицу, и он распадается на строки.

Краси