Страницы: 1
RSS
Power Query. Прогнозные значения свойств на будущие периоды
 
Здравствуйте, уважаемые знатоки!
Есть таблица фактов с продажами товара по месяцам(по горизонтали) и поведением его свойства с течением времени(по вертикали)
На основании этих данных строю прогнозные значения свойств на будущие периоды
При этом периоды с 2-4, 5-7 и 8-12 имеют разные алгоритмы планирования
Подскажите, пожалуйста, возможно ли реализовать на PQ построение такого прогноза? Пока даже не понимаю, с какой стороны подойти и есть ли шансы
 
Так надо?
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    Coeff = List.Buffer( Источник[#"к-т"] ),
    ListExample = List.Buffer( Table.RemoveColumns( Источник,{"Срок жизни", "к-т"})[май.19] ), //это ненужный шаг, только для демонстрации как работает функция с одним списком, его можно удалить
    Count = Table.RowCount(Источник),
    fn = (List)=>
    List.Generate(
        ()=>[i = 0, l = {List{i}}], 
        each [i] < Count, 
        each [i= [i] + 1, l = [l] & {if List{i}=null or List{i}="" 
                                        then if i<7
                                            then List.Last([l]) * Coeff{i} 
                                            else List.Average( List.LastN([l],3) )
                                        else List{i}}], 
        each List.Buffer([l])){Count-1},
    Ex = fn(ListExample), //это ненужный шаг, только для демонстрации как работает функция с одним списком, его можно удалить
    Out = Table.FromColumns( Table.ToColumns( Источник[[Срок жизни], [#"к-т"]] ) & List.Transform( Table.ToColumns( Table.RemoveColumns(Источник,{"Срок жизни", "к-т"}) ), fn ), Table.ColumnNames(Источник) )
in
    Out
Изменено: PooHkrd - 01.04.2020 00:51:47
Вот горшок пустой, он предмет простой...
 
PooHkrd, огромное спасибо! Как всегда, исполнение на высшем уровне!
Можно, пожалуйста, уточнить - возможно ли расчет к-тов(coef) также добавить в скрипт?
При этом они привязаны к последним 3 отчетным датам. Т.е. при добавлении отчетных дат, формула расчета к-тов "сдвигается вправо"
Я сразу застрял на этом этапе...(
 
Цитата
Student64 написал:
возможно ли расчет к-тов(coef) также добавить в скрипт?
С виду там ничего сложного, но сейчас времени нет, позже.
Т.е. в идеале в исходной таблице коэффициентов не должно быть, их нужно считать на лету, так?
Вот горшок пустой, он предмет простой...
 
Цитата
Т.е. в идеале в исходной таблице коэффициентов не должно быть, их нужно считать на лету, так?
PooHkrd, да, верно
 
PooHkrd, если вдруг найдется минутка, загляните, пожалуйста
 
Как-то так получается:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица14"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Источник, {"Срок жизни"}, "Атрибут", "Значение"),
    PlusOne = Table.TransformColumns(Unpivot, {{"Срок жизни", each _ + 1, type number}}),
    Join = Table.Join( PlusOne, {"Срок жизни", "Атрибут"}, Table.RenameColumns(Unpivot,{{"Значение", "Значение2"}}), {"Срок жизни", "Атрибут"}),
    Filter = Table.SelectRows(Join, each ([Значение2] <> "")),
    Group = Table.Group(Filter, {"Срок жизни"}, {{"Коэф", each List.Average(List.LastN([Значение2],3)) / List.Average(List.LastN([Значение],3)), type number}}),
    First3 = List.Buffer( List.FirstN( Group[Коэф], 3 ) ),
    Coeff = List.Buffer( {null} & List.Generate( ()=>[i = 0, l = First3], each [i]<=3, each [i=[i]+1, l=[l] & {List.Average(List.LastN([l],3))} ], each [l] ){3} ),
    Count = Table.RowCount(Источник),
    fn = (List)=>
    List.Generate(
        ()=>[i = 0, l = {List{i}}], 
        each [i] < Count, 
        each [i= [i] + 1, l = [l] & {if List{i}=null or List{i}="" 
                                        then if i<7
                                            then List.Last([l]) * Coeff{i} 
                                            else List.Average( List.LastN([l],3) )
                                        else List{i}}], 
        each List.Buffer([l])){Count-1},
    Out = Table.FromColumns( Table.ToColumns( Источник[[Срок жизни]] ) & List.Transform( Table.ToColumns( Table.RemoveColumns(Источник,{"Срок жизни"}) ), fn ), Table.ColumnNames(Источник) )
in
    Out
Вот горшок пустой, он предмет простой...
 
как-то так получилось, но не обошлось без IEEE 754
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    buf = List.Buffer(Table.ToRows(Table.RemoveColumns(Source,{"Срок жизни", "к-т"}))),
    col = List.Count(buf{0}),
    fn1 = (a,i)=> Number.Power(List.Average(List.Range(buf{a+i},col-4-a,3)),Number.Power(-1,1-i)),
    fn2 = (a)=> List.Product(List.Transform({1,0},each fn1(a,_))),
    fn3 = (l,n)=> List.Accumulate({1..n},l,(a,b)=>a&{List.Average(List.LastN(a,3))}),
    coef = List.Buffer(fn3(List.Generate(()=>0,each _<3,each _+1, fn2),3)),
    fn4 = (n,k)=> List.Generate(()=>[i=n-1,v=k*coef{i}],each [i]<6, each [i=[i]+1,v=[v]*coef{i}],each [v]),
    fill = List.Transform(List.Zip(buf),
        (_)=>let 
            a = List.PositionOf(_,""),
            b = List.RemoveLastN(_,each _=""),
            c = b&fn4(a,_{a-1}), 
            d = if a>6 then b else c,
            g = if a=-1 then _ else fn3(d,List.Count(buf)-List.Count(d))
        in g
    ),
    ret = Table.FromColumns({{1..List.Count(buf)},{null}&coef}&fill,Table.ColumnNames(Source))
in
    ret
Изменено: Андрей Лящук - 02.04.2020 10:21:50
 
PooHkrd, большое спасибо!!! Чудесно!!
 
Андрей Лящук, спасибо большое, что откликнулись!!
Выглядит "немного" сложнее для моего уровня:)  
 
PooHkrd, можно, пожалуйста, несколько вопросов по скрипту

1. Зачем применяется функция List.Buffer ? Попробовал без нее - результат не изменился
2. Я запутался в последовательности выполнения шагов List.Generate (статьи Максима Зеленского и Сергея Лосева читал)
  Прогоняем каждый столбец через List.Generate
  На старте берем 1 элемент столбца
  Проверяем условие, что индекс не превышает границ столбца
  Дальше не понимаю, какой шаг начинает работать - selector или next ? (я считал что selector применяется в самом конце, после того как сформированы все элементы списка, но сейчас сомневаюсь)
  и в чем суть этого шага List.Buffer([l])){Count-1} ?
 

 
 
 
Student64, если вы про этот кусок
Код
First3 = List.Buffer( List.FirstN( Group[Коэф], 3 ) ),
Coeff = List.Buffer( {null} & List.Generate( ()=>[i = 0, l = First3], each [i]<=3, each [i=[i]+1, l=[l] & {List.Average(List.LastN([l],3))} ], each [l] ){3} ),

то там, насколько я понимаю, происходит следующее:
1. Берем первые 3 значения из столбца Коэф как список (First3)
2. Далее создаем запись из счетчика i и l=Frist3
3. Проверяем условие счетчика
4. Если удовлетворяет, срабатывает селектор - кладем первым элементом списка l
5. Увеличиваем счетчик и создаем следующий l, добавляя к нему среднее из трех последних элементов
6. Проверяем счетчик, если удовлетворяет - кладем l как следующий элемент списка. Переходим к п.5

и так получаем 4 элемента нового списка, из которого берем последний. Не вдавался в детали, зачем там еще {null} дописываем впереди
То есть, по сути, так работает:

seed
condition
selector

next
condition
selector

next
condition
selector
...
F1 творит чудеса
 
Максим Зеленский, большое спасибо!
Вообще по другому представлял работу этого цикла.
Я думал, что он бегает по исходному столбцу First от первого элемента до последнего и приклеивает снизу новый элемент.
Сейчас понимаю, что на каждом шаге создается новый список

Можно еще, пожалуйста, объяснить, зачем нам здесь selector, если он никаких преобразований не совершает?(пытался его сам убрать, но выдал ошибку)
И также вопрос про List.Buffer - зачем он здесь нужен?
 
Так захотел автор ))
selector в данном случае собирает список списков.
Если убрать selector, то результатом List.Generate был бы список записей, поле l которых содержало бы список. И вместо {3} в конце надо было бы написать {3}[l], т.е. из четвертого элемента списка взять поле записи l
F1 творит чудеса
 
Максим Зеленский, большое спасибо за подробные разъяснения!
 
PooHkrd, Алексей, подскажите, пожалуйста, как нужно изменить условие в 15 строке из поста #7, чтобы критерием прогнозных расчетов было не пустое значение в исходном файле, а дата анализируемого периода?
Т.е. условие типо Date.AddMonths(Название столбца, Срок-1) > Текущий период
Текущий период - максимальная дата из заголовков. На нашем примере - окт'19
Не пойму, как обратиться к названию столбца из функции...
Возможно, нужно добавлять месяц как еще один аргумент функции, но тоже не смог внедрить его в 21 строку, где идет вызов функции.
 
Student64, не, так я не играю.
Давайте конкретный пример исходника, и конкретные условия "что изменилось". Так на пальцах это не работает.
Тем более вона вы какие красивые картинки со стрелочками рисовать умеете!  :D
Цитата
Максим Зеленский написал:
Так захотел автор ))
Просто нравится мне как он пишется. Вот и пихаю куда ни попадя. Там он действительно не нужен.
Изменено: PooHkrd - 23.04.2020 17:05:31
Вот горшок пустой, он предмет простой...
 
Добавил новый расчет в строки 35-47
Разница на примере 4 срока жизни для марта'19 - т.е. если за прошедший фактический период нет данных, то прогноз к этому периоду не применяем, ставим 0.
 
так нужно?
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    buf = List.Buffer(Table.ToRows(Table.RemoveColumns(Source,{"Срок жизни", "к-т"}))),
    col = List.Count(buf{0}),
    fn1 = (a,i)=> Number.Power(List.Average(List.Range(buf{a+i},col-4-a,3)),Number.Power(-1,1-i)),
    fn2 = (a)=> List.Product(List.Transform({1,0},each fn1(a,_))),
    fn3 = (l,n)=> List.Accumulate({1..n},l,(a,b)=>a&{List.Average(List.LastN(a,3))}),
    coef = List.Buffer(fn3(List.Generate(()=>0,each _<3,each _+1, fn2),3)),
    fn4 = (n,k)=> List.Generate(()=>[i=n-1,v=k*coef{i}],each [i]<6, each [i=[i]+1,v=[v]*coef{i}],each [v] ),
    fill = List.Transform(List.Zip(buf),
        (_)=>let 
            a = List.RemoveLastN(_,each _="" or _=null),
            b = List.Count(a),
            c = List.Transform(a,each if _=null or _="" then 0 else _),
            d = c&fn4(b,_{b-1}), 
            e = if b>6 then c else d,
            f = if a=-1 then _ else fn3(e,List.Count(buf)-List.Count(e))
        in f
    ),
    ret = Table.FromColumns({{1..List.Count(buf)},{null}&coef}&fill,Table.ColumnNames(Source))
in
    ret
 
Андрей Лящук, спасибо! попробую разобраться)
 
Добрый день уважаемые гуру!
Подниму тему.
Возникла похожая задача - прогноз показателей исходя из статистического факта. Попыталась оптимизировать код из сообщений выше, но что-то запуталась и дошла только до коэффициентов, и то частично.
Помогите пожалуйста!
Файл прилагаю.
Страницы: 1
Наверх