Как агрегировать данные по номенклатуре и отбросить отриц-е показатели

Vasser · 2019-05-27 15:57:57

Здравствуйте!
Столкнулся с проблемой.
Не хватает фантазии для расчета итоговых показателей (вычислить суммарные излишки в разрезе по статусу товара).
Если излишек у номенклатуры отрицательный, мы его в общую сумму не добавляем, плюсуем только товары с положительным излишком. Формула работает по измерению «Номенклатура», но не работает по измерению "Статус товара". Т.к. в разрезе по статусу плюсует излишки всех товаров с одинаковым статусом, в том числе и номенклатуры с отрицательным излишком.
Подскажите пожалуйста как отбросить отрицательные излишки по измерению "Номенклатура" при расчете суммарных излишков по измерению "Статус товара"?

Формула расчёта излишка:
If(Излишек>0, Излишек, ‘Отсутствует’)

Где "Излишек" = ((Sum([Остаток])+ //ИТОГО ОСТАТКИ. Сумма всех остатков + товары в пути между складами. В разрезе по номенклатуре =sum([Остаток]+ [К получению]), где [Качество]='новый'
sum({<[Дата прибытия (Estimated date).autoCalendar.MonthsAgo]={0}>}[Остаток в пути (on the way)])+ //В ПУТИ ИМПОРТ. В разрезе по номенклатуре =sum[Остаток в пути (on the way)], где Дата прибытия (Estimated date)= текущий месяц
sum({<[Дата прибытия (Estimated date).autoCalendar.TwoMonth]={0}>}[Остаток в пути (on the way)])+ //В ПУТИ ИМПОРТ. В разрезе по номенклатуре =sum[Остаток в пути (on the way)], где Дата прибытия (Estimated date)= + следующий месяц
sum({<[Дата прибытия (Estimated date).autoCalendar.ThreeMonth]={0}>}[Остаток в пути (on the way)])+ //В ПУТИ ИМПОРТ. В разрезе по номенклатуре =sum[Остаток в пути (on the way)], где Дата прибытия (Estimated date)= + через месяц
sum([Готово к перевозке (ready for shipment)])+ // В ПРОИЗВОДСТВЕ ГОТОВО. В разрезе по номенклатуре =Sum[Готово к перевозке (ready for shipment)]
sum([Остаток в производстве (in production)]))- //В ПРОИЗВОДСТВЕ ОПЛАЧЕНО. В разрезе по номенклатуре =sum[Остаток в производстве (in production)]
(sum({<[OTP_Sales.%дата.autoCalendar.MonthsAgo]={0},[OTP_Sales.Тип данных]={'ПФА_ПП'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ (5 мес) текущий месяц
sum({<[OTP_Sales.%дата.autoCalendar.TwoMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПП'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ (5 мес) следующий месяц
sum({<[OTP_Sales.%дата.autoCalendar.ThreeMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПП'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ (5 мес) ...
sum({<[OTP_Sales.%дата.autoCalendar.FourMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПП'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ (5 мес) ...
sum({<[OTP_Sales.%дата.autoCalendar.FiveMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПП'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ (5 мес) План продаж на 4-й месяц вперед от текущей даты
sum({<[OTP_Sales.%дата.autoCalendar.MonthsAgo]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). Текущий месяц
sum({<[OTP_Sales.%дата.autoCalendar.TwoMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). Следующий месяц
sum({<[OTP_Sales.%дата.autoCalendar.ThreeMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.FourMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.FiveMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.SixMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.SevenMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.EightMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.NineMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.TenMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.ElevenMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])+ //ИТОГО ПЛАНЫ АКЦИИ (12 мес). ...
sum({<[OTP_Sales.%дата.autoCalendar.TwelveMonth]={0},[OTP_Sales.Тип данных]={'ПФА_ПА'}>}[OTP_Sales.План])))

Редактировался Vasser (2019-05-27 15:59:15)

Bao · 2019-05-27 18:49:46

Добрый день, вместо If(sum([Остаток]+ [К получению])>0, sum([Остаток]+ [К получению]), ‘Отсутствует’), думаю надо
sum (If(([Остаток]+ [К получению])>0, ([Остаток]+ [К получению]), 0)) и уже к сумме ставить условие, если она отрицательна, то 'отсутствует'

Редактировался Bao (2019-05-27 19:31:45)

Vasser · 2019-05-28 13:07:20

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

Добавлено спустя 16 мин 38 с:
Напишу свою формулу проще.

If((Sum([Остаток])+sum([Остаток в пути]))-(sum( [ИТОГО ПЛАНЫ] + sum([ИТОГО ПЛАНЫ АКЦИИ]))>0,(Sum([Остаток])+sum([Остаток в пути]))-(sum( [ИТОГО ПЛАНЫ]+sum([ИТОГО ПЛАНЫ АКЦИИ]), 'Отсутствуют').

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

Редактировался Vasser (2019-05-28 15:07:56)

Bao · 2019-05-28 16:59:32

Должно помочь. Включая сумму внутрь условия Вы сам сообщаете Клику необходимость сначала все просуммировать в рамках выборки, а затем делать вывод отрицательная ли уже общая сумма или нет. Расчет верно идет не только по измерению Номенклатура, а в разрезе Номенклатура+Статус, так как это частная выборка суммировать в которой по сути и нечего. Как только вы выкинете Статус, я предполагаю, что сумма по Номенклатуре тоже поплывет.
//Попробуем сделать простой пример:
LOAD * INLINE [
Item, State, Balance, Arrival
Item1,Active , -10, 2
Item1,Old , -20, 2
Item2,Active , 10, 2
Item2,Old , -15, 2

];
Сделаем по ней Прямую таблицу с одним измерением Item, по Item1 должны получить везде 0 (ну т.е. по сути "откинуть" отрицательные значения для дальнейших операций), по Item2 при любой выборке 12:
Item    =(sum (if (((Balance+Arrival))>0,(Balance+Arrival),0))) =sum (Balance)+sum (Arrival)
Item1    0    -26
Item2    12    -1

во втором выражении специально условие не ставил, что было видно неверную сумму при отсутствии разреза по измерению Статус,
а если добавить - то и второе выражение будет верно отображать сумму (что, как я думаю у вас и происходит):

Item State    =(sum (if (((Balance+Arrival))>0,(Balance+Arrival),0))) =sum (Balance)+sum (Arrival)
Item1 Old    0    -18
Item1 Active    0 -8
Item2 Old    0    -13
Item2 Active    12    12

А теперь уберем измерение Номенклатуры, оставим только Статус:

State =(sum (if (((Balance+Arrival))>0,(Balance+Arrival),0))) =sum (Balance)+sum (Arrival)
Old 0 -31
Active 12 4

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

Редактировался Bao (2019-05-28 17:02:02)

Неофициальный форум разработчиков QlikView и Qlik Sense

#1 2019-05-27 15:57:57

Как агрегировать данные по номенклатуре и отбросить отриц-е показатели

#2 2019-05-27 18:49:46

Re: Как агрегировать данные по номенклатуре и отбросить отриц-е показатели

#3 2019-05-28 13:07:20

Re: Как агрегировать данные по номенклатуре и отбросить отриц-е показатели

#4 2019-05-28 16:59:32

Re: Как агрегировать данные по номенклатуре и отбросить отриц-е показатели

Подвал форума