Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Не получили письмо с кодом активации?
Ноябрь 21, 2017, 07:33:22 am

Автор Тема: Помогите написать выражение для построения графика в Qlikview  (Прочитано 7446 раз)

Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Добрый день.

Только начал самостоятельно осваивать Qlick View - соответственно пока имею лишь поверхностное представление о его возможностях. Заранее прошу прощения за, как кому то может показаться, "ламерские" вопросы.

Суть задачи: имею загруженную таблицу (см.рис).  Необходимо, чтобы при выборе из списка того или иного месяца на гистограмме или в таблице отражался определенный коэффициент для каждого TYPE.

Коэффициент достаточно "замороченный". Считается как ((A+B)-C) / (D+E), где:

A - кол-во  компаний с ХОТЯ БЫ ОДНИМ  статусом офиса "ACTIVE" за месяц January 14
B - кол-во  компаний у которых  ВСЕ офисы имеют статус "NEW" за месяц January 14
C - кол-во  компаний у которых  ВСЕ офисы имеют статус "NEW" за период с January 14 по выбранный в списке месяц включительно
D - кол-во  компаний с ХОТЯ БЫ ОДНИМ  статусом офиса "ACTIVE" за выбранный в списке месяц
E - кол-во  компаний у которых  ВСЕ офисы имеют статус "NEW" за выбранный в списке месяц

Буду очень признателен за любую помощь и советы относительно того как можно решить эту задачу.

Заранее благодарен.

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
Добрый день.
Можете подготовить контрольный пример?
Таблицу-исходник в xls и рассчитанные показатели для сверки.

Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Спасибо за оперативность. Конечно подготовлю. По готовности - выложу.

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


Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Прикрепил исходный файл.

Уточнение - немного напутал с формулой коэффициента. Правильный вариант (((D+E)-C) / (A+B))*100%

В итоге, если в списке выбрать December 14, то должны получиться след.значения коэффициента:

FEDERAL (86%)
INDEPENDED (88%)
REGIONAL (52%)

Еще раз спасибо за помощь.

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
ОК, интересная задача.
Я так понимаю, затруднение в определении количества < компаний у которых  ВСЕ офисы имеют статус "NEW"> ?
Пока могу только ссылку на теорию дать
С практикой, только на выходных .

Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Спасибо за ссылку - буду изучать.

Проблема у меня несколько шире - отсутствует понимание синтаксиса необходимого выражения в принципе (с SQL знаком предельно поверхностно) :)




Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
SQL там совсем немного.
В вашем случае надо правильно загрузить таблицу, с преобразованием значения периода в формат. пригодный для обработки.
Затем надо будет составить выражение. Оно довольное сложное, для новичка.
Но если разбить работу на этапы, то все окажется намного проще.
Попозже посмотрю.

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


Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
Вопрос по параметру С.
Считаются все организации кто хотя бы раз имел ситуацию как для параметра B ?
Т.е. организации, имеющие хоть раз ситуацию, когда все офисы имели статус New в один из периодов идут в подсчет?

Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Да, все верно. В параметре С считаются все компании, в которых хотя бы раз все офисы были со статусом New, за период с начала года (January 14) по выбранный месяц.
Грубо говоря,  необходимо посчитать сколько у нас появилось новых компаний.

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
Дмитрий, извиняюсь за задержку.
Во вложении таблица с показателем по каждому типу и периоду, но на ваши контрольные цифры не выходит.
Посмотрите по таблице параметры, правильно рассчитаны или нет.

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
Вкратце, по задаче.
Первым делом, при загрузке следует преобразовать значение поля PERIOD в формат, пригодный для сортировки.
map1:
mapping LOAD PERIOD, dual(month(FirstDay) & '-' & year(FirstDay),year(FirstDay) & num(month(FirstDay), '00')) as 'Месяц-Год' inline [
PERIOD,FirstDay
January 14, 01.01.2014
February 14, 01.02.2014
March 14, 01.03.2014
April 14, 01.04.2014
May 14, 01.05.2014
June 14, 01.06.2014
July 14, 01.07.2014
August 14, 01.08.2014
September 14, 01.09.2014
October 14, 01.10.2014
November 14, 01.11.2014
December 14, 01.12.2014
 ];

tmp1:
LOAD num(ApplyMap('map1',PERIOD,'--')) as PERIODNum,
ApplyMap('map1',PERIOD,'--') as PERIOD,
PERIOD as PERIODOld,
     TYPE,
     COMPANY,
     [CITY OFFICE],
     STATUS
FROM
[таблицы.xlsx]
(ooxml, embedded labels, table is Лист1);
Три поля с периодом для понимания.
Затем по, условию задачи нам надо будет высчитать сколько организаций имели офисов в статусе только NEW, т.е. не имея остальных статусов. На мой взгляд, проще всего добавить к колонкам таблицы флажок с количеством статусов для каждой организации.
Поэтому к вышеуказанному коду добавляем:
tmp2:
//NoConcatenate
left join LOAD PERIOD,
     TYPE,
     COMPANY,
Count(DISTINCT STATUS) as STATUS_ALL
Resident tmp1
Group by PERIOD,TYPE, COMPANY;
тем самым, мы упрощаем себе задачу в определении параметра E
count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY)
Для отладки выражений загрузим таблицу в приложение и создадим диаграмму в виде прямой таблицы.
Измерения - TYPE, PERIOD
Выражения, начнем с D
Нам надо подсчитать количество компаний, где есть хотя бы один статус ACTIVE хоть в одном офисе.
Языком функций это будет выглядеть так:
Count(DISTINCT {<STATUS={"ACTIVE"}>} COMPANY)
Для одного месяца:
Count(DISTINCT {<PERIODNum={'201401'},STATUS={"ACTIVE"}>} COMPANY)
Это у нас выходит параметр A
Далее, по аналогии выходим на параметр B
count(DISTINCT {<PERIOD={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY)
Параметр C представляет собой E, с ежемесячным накоплением по каждому TYPE.
RangeSum(Above(count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()))

Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Спасибо огромное. С контрольными показателями действительно - я напутал. У вас все верно.

Буду разбираться с премудростями редактирования скрипта ))

А из того, что прямо сейчас не понимаю:

1) все перечисленные вами правки скрипта внес
2) создал на листе список из PERIODNum
3) создал диаграмму(гистограмму, измерение TYPE и PERIOD) где прописал выражение:

((Count(DISTINCT {<STATUS={"ACTIVE"}>} COMPANY) + Count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY))- RangeSum(Above(Count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo())))/(Count(DISTINCT {<PERIODNum={'201401'},STATUS={"ACTIVE"}>} COMPANY) - Count(DISTINCT {<PERIOD={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY))

В итоге, все идеально работает, НО только если в списке выбрать период 201401. При выборе любого другого периода на графике лишь "Нет данных для отображения" :(

В чем может быть дело?

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
 :o
Я в рабочих перерывах пишу и не все еще выложил, будут нюансы.
Но если работает, значит на верном пути  :)

Оффлайн Дмитрий

  • Новичок
  • *
  • Сообщений: 17
  • Рейтинг: +2/-0
    • Просмотр профиля
Сорри за назойливость :) 

Оффлайн admin

  • Administrator
  • Hero Member
  • *****
  • Сообщений: 981
  • Страна: ru
  • Рейтинг: +97/-0
    • Просмотр профиля
Ниже снимок отладочной таблицы.
Я предпочитаю разбивать формулы, по возможности. Проще для понимания ( и себе и тем кто с кодом будет работать).
Поэтому ввожу в скрипт переменные:
set vA=RangeSum(Above(Count(DISTINCT {<PERIODNum={'201401'},STATUS={"ACTIVE"}>} COMPANY),0,RowNo() ));
set vB=RangeSum(Above(count(DISTINCT {<PERIODNum={'201401'},STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()));
set vC=RangeSum(Above(count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY),0,RowNo()));
set vD=Count(DISTINCT {<STATUS={"ACTIVE"}>} COMPANY);
set vE=count(DISTINCT {<STATUS={"NEW"},STATUS_ALL={'1'}>} COMPANY);

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