Неофициальный форум пользователей Qlik Sense & Qlikview

Для разработчиков => Вопросы по Qlik Sense & QlikView => Тема начата: Софья от 16 сентября 2015, 08:01:35

Название: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 08:01:35
Всем добрый день.

Возникла новая задача. Начну по порядку.

Требуется рассчитать средний остаток, причём, двумя способами.Первый расчёт - в нём нужно учесть дни, когда остаток = 0. А второй расчёт не должен учитывать дни, когда остаток = 0.

Пример для наглядности:
Допустим, за 1 мая остаток 50 штук, за 2 мая – 30 штук, за 3 мая – 0 штук, за 4 мая – 20 штук. Фактически, при первом расчёте средний остаток будет равен (50+30+0+20)/4=25. А при втором расчёте - (50+30+0+20)/3=33,3.

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

У меня средний остаток рассчитывается с помощью Set Analysis вот такой конструкцией:num(sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL)
-sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL2), '# ##0,00')

где KOL – количество по приходным документам, а KOL2, соответственно, по расходным.

Формула AVG() не работает поверх данной конструкции. Дробление же её я не могу вообразить, т.к. только при помощи данной конструкции средний остаток верно рассчитывается.

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

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


Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: bibis от 16 сентября 2015, 10:47:17
avg(
aggr(
num(sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL)
-sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL2), '# ##0,00')
,floor(DOC_DATE)
)

Хотя лучше считайте остаток на каждый день в скрипте.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: kvv от 16 сентября 2015, 10:55:15
Добрый день Софья.
Первый показатель, там где разделить на 4 дня посчитать более легко, нужно взять количество дней: count(Date).
Второй показатель, там где разделить на 3 дня, можно посчитать вот так:
count({$<QUANT_STOCK = {'>0'}>}QUANT_STOCK).

В целом, можно второй показатель посчитать и на этапе загрузке, например:
if (QUANT_STOCK > 0, 1, 0) as QUANT_STOCK_IS
И дальше, в дашборде, считаем суммой QUANT_STOCK_IS.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 11:04:03
Большое спасибо ответившим, сейчас попробую реализовать согласно первой рекомендации, и согласно второй.

Bibis, Вы имели в виду, что средний остаток лучше считать в скрипте?

Kvv, а можно поподробнее, что значит считать в "дашборде"?
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: kvv от 16 сентября 2015, 11:08:16
Цитата: Софья от 16 сентября  2015, 11:04:03  
Большое спасибо ответившим, сейчас попробую реализовать согласно первой рекомендации, и согласно второй.

Bibis, Вы имели в виду, что средний остаток лучше считать в скрипте?

Kvv, а можно поподробнее, что значит считать в "дашборде"?

В дашборде, это имел ввиду в QlikView. :)
Прикрепленный файл - 01.png.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 11:26:03
Kvv, спасибо за просвещение.)
Буду пробовать, о результатах отпишусь чуть позже.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 11:54:02
Цитата: bibis от 16 сентября  2015, 10:47:17  
avg(
aggr(
num(sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL)
-sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL2), '# ##0,00')
,floor(DOC_DATE)
)

Хотя лучше считайте остаток на каждый день в скрипте.

Bibis, позвольте задать вопрос, что означает floor(DOC_DATE)? Посчитала, итог прикрепляю в приложении. Формула не работает, но и ошибок не показывает. В чём может быть проблема?
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: bibis от 16 сентября 2015, 12:13:52
floor(DOC_DATE) по сути ваша дата.
Т.е. формула должна считать ваше выражение для каждого дня и выдавать среднее.
Пока не понимаю,почему не работает.буду думать...если работать не заставят)
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 12:27:22
Просто не сталкивалась ещё в работе с оператором floor(), поэтому стал интересен его функционал. В любом случае спасибо, тоже буду думать.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: bibis от 16 сентября 2015, 12:32:54
В общем разобрался вроде бы. aggr  судя по всему не группирует по выражениям (странно,раньше не замечал), так что  этот самый Floor(DOC_DATE) считаем сразу в скрипте, обзываем как-нибудь и уже это имя втыкаем в формулу.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 01:00:44
Можно уточнить, как посчитать floor(DOC_DATE) в скрипте? Не совсем ясен синтаксис его расчётов.
Хочу понять, в чём разница между полем DOC_DATE и floor(DOC_DATE), что именно возвращает данный оператор.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: bibis от 16 сентября 2015, 01:20:45
Это просто округление вниз.  Т.е. выдает дату в  числовом формате. В принципе с тем же успехом можно и
date(DOC_DATE, 'YY.MM.DD')  использовать.

А как считать: просто, в табличке, где грузите данные   подгружайте еще расчетное поле:

Load , KOL,KOL2,DOC_DATE, FLOOR(DOC_DATE) as дата
from чтототтам;

Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 16 сентября 2015, 01:36:49
Bibis, спасибо, теперь всё ясно.)
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 28 сентября 2015, 06:35:07
Цитата: kvv от 16 сентября  2015, 10:55:15  

Второй показатель, там где разделить на 3 дня, можно посчитать вот так:
count({$<QUANT_STOCK = {'>0'}>}QUANT_STOCK).


Добрый день ещё раз.
Kvv, к Вам с вопросом обращаюсь.
Спасибо, остаток, где 0 тоже считается, посчитала.
Но возникает вопрос по второй формуле, где нужно исключить 0. Формула рабочая, выдаёт какой-то результат, но он некорректный.
Суть в том, что у меня остаток: это приходные документы - расходный документы (KOL-KOL2). Т. е. нет такого поля, как остаток, я его убрала. Остаток рассчитываю в выражениях (знаю, что влияет на быстродействие системы), но когда прогружала данную разницу через скрипт - результат был некорректный. Причину так и не нашла на данный момент.
Вкладываю скрин для наглядности.
Последняя колонка, "Без 0", должна содержать совсем другие цифры, они намного меньше должны быть.
В чём может быть проблема?
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 28 сентября 2015, 06:38:12
Цитата: kvv от 16 сентября  2015, 10:55:15  

В целом, можно второй показатель посчитать и на этапе загрузке, например:
if (QUANT_STOCK > 0, 1, 0) as QUANT_STOCK_IS


Данная формула у меня не работает, когда в выражениях её прописываю. Связано ли это с тем, что я её не в скрипте писала? Я правильно полагаю, что она и в выражениях должна работать, и на этапе прогрузки кода?
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: kvv от 28 сентября 2015, 11:08:13
Добрый день, Софья.

1.
Цитата: Софья от 28 сентября  2015, 06:38:12  
Связано ли это с тем, что я её не в скрипте писала? Я правильно полагаю, что она и в выражениях должна работать, и на этапе прогрузки кода?

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

2. А не может быть следующие, вот если на конец дня 28.09.2015 остаток Морковки равно 0 и Морковку не привезли 29.09.2015. Какой будет остаток на конец дня Морковки на 29.09.2015???
Подозреваю, что Морковки вообще не будет в таблице остатков. Что скажете???

P.s. Сейчас очень занят, возможно не смогу оперативно отвечать на форуме. :(
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 28 сентября 2015, 01:04:19
Остаток по товару всегда цепляется за предыдущий день, т. е. если 25-го был остаток, допустим, 45, 26-го 100 (был приход в этот день, не суть), а 27-го - 0, 28-го не было прихода, то остаток по-прежнему 0. И будет 0, пока не будет новой поставки.
Поэтому остаток морковки на конец 29.09.2015 будет = 0.
Группы товаров всегда отображаются в общем списке, неважно, какой остаток. Даже если на определённую дату остаток равен 0. Здесь важно проследить динамику потребления, есть ли у неё сезонность, да и вообще, какие товары лучше продаются, а какие не стоит держать на складе, возможно, убрать из ассортимента.

P.S. Я всё понимаю, что у всех свои задачи есть, поэтому принимаю любую "оперативность" отвечающего.)
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: kvv от 02 октября 2015, 05:13:49
После некоторого перерыва, продолжим.
1. Остаток где 0 уже посчитан. Верно?
Можно посмотреть формулу, как считается?

2. Неужели, у вас "тянуться" все нулевые остатки. То есть, Морковка на 02.10.2015 вся продалась и ее не завозили 2 месяца и это все время, на остатках будет показываться Морковка с нулевым остатком?
Все же, мне кажется что такого не может быть. Конечно, могу ошибаться.

3. В целом, посчитать остаток, где товар есть на остатке намного легче, чем если бы товара не было на остатке.

P.s. Так получилось, что недавно занимался и собственно сейчас занимаюсь похожей задачей. Мне интересен данный вопрос - давайте постараемся довести его до логического конца. :)
P.s.s. Если не сложно приведите пример с БД, 4-5 товаров за какой-то период(10-20 дней). Желательно, чтобы были как нулевые остатки так и не нулевые.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 05 октября 2015, 05:40:10
Цитата: kvv от 02 октября  2015, 05:13:49  
После некоторого перерыва, продолжим.
1. Остаток где 0 уже посчитан. Верно?
Можно посмотреть формулу, как считается?

2. Неужели, у вас "тянуться" все нулевые остатки. То есть, Морковка на 02.10.2015 вся продалась и ее не завозили 2 месяца и это все время, на остатках будет показываться Морковка с нулевым остатком?
Все же, мне кажется что такого не может быть. Конечно, могу ошибаться.

3. В целом, посчитать остаток, где товар есть на остатке намного легче, чем если бы товара не было на остатке.

P.s. Так получилось, что недавно занимался и собственно сейчас занимаюсь похожей задачей. Мне интересен данный вопрос - давайте постараемся довести его до логического конца. :)
P.s.s. Если не сложно приведите пример с БД, 4-5 товаров за какой-то период(10-20 дней). Желательно, чтобы были как нулевые остатки так и не нулевые.

1. Да, конечно.
num(sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, YEAR=, MONTH=, NUMBE=>} KOL)/COUNT(DISTINCT DOC_DATE & MONTH), '# ##0,00')

2. Всё верно понимаете - 0-е остатки тянутся, пока не привозят товар. Просто на практике таких простоев - несколько месяцев - с нулевым остаток не бывает. Если только пару дней. А если товар плохо брали, и тут забрали последний, то, скорее всего, его уже завозить не будут. Просто снимают с ассортимента. Я это к тому, что товары в подавляющем большинстве ходовые, и поставки их, оперативные, соответственно.

3. Согласна с Вами, что проще бы было. Но требуют именно такие данные.

4. Дико извиняюсь, сама отвечаю сейчас не оперативно - много других задач.

5. Данные мне предоставить несложно, но обычно за такой короткий период 0-х остатков не бывает. Сейчас задумалась над Вашим предложением, и пока не могу найти подобный пример - остаток приличный, либо отрицательный. Да, и отрицательный он может быть - но это уже отдельная история.)
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: kvv от 05 октября 2015, 01:18:06
Добрый.
1. Наверное, вот так можно попробовать:
num(sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, KOL = {'>0'}, YEAR=, MONTH=, NUMBE=>} KOL) / count({$ <KOL = {'>0'}>}distinct DOC_DATE & MONTH), '# ##0,00')

2. ХМ, есть таблица ассортимента. Тоже вариант и даже нормальный вариант. Не подумал о таком варианте сначала.

5. Если формула с пункта 1 верно все посчитает, то по идее, данные с примером не нужно высылать.
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: Софья от 12 октября 2015, 08:14:50
Цитата: kvv от 05 октября  2015, 01:18:06  
Добрый.
1. Наверное, вот так можно попробовать:
num(sum({$ <DOC_DATE = {'>=$(=yearstart(date(min(DOC_DATE),-1)))<=$(=date(max(DOC_DATE)))'}, KOL = {'>0'}, YEAR=, MONTH=, NUMBE=>} KOL) / count({$ <KOL = {'>0'}>}distinct DOC_DATE & MONTH), '# ##0,00')

Извиняюсь за столь продолжительное отсутствие, были задачи, которые требовали решения.

Формула выдаёт 0. Ошибок, когда её прописываю в выражениях, нет (красным ничего не подчёркивает).
Название: Re: Расчёт среднего остатка с учётом и без учёта нулевого остатка
Отправлено: kvv от 12 октября 2015, 01:34:31
Тогда, давайте попробуем с реальным примером.

P.s.s. Если не сложно приведите пример с БД, 4-5 товаров за какой-то период(10-20 дней). Желательно, чтобы были как нулевые остатки так и не нулевые.