Média ponderada de apenas valores válidos em níveis diferentes

[#1]

Bom dia a todos,

Estou com dificuldade de transformar um cálculo realizado no Excel para o QlikView. Preciso apresentar os resultados de uma avaliação, tenho todos reunidos em uma base em que existem diversas dimensões a serem analisadas, como por exemplo: período de avaliação, fornecedor, macro critérios (com respectivos pesos) e critérios (com respectivos pesos).

Algumas regras que tem dificultado minha análise:

- A cada critério é atribuída uma nota, que varia de 0 a 10 ou pode ser "Não aplicável". Quando "Não aplicável", o peso do critério é distribuído dentre os demais Critérios do Macro critério, a fim de obter a média ponderada dos critérios válidos.

- Quando todos os Critérios do Macro Critério forem "Não Aplicáveis", o peso do Macro Critério é distribuído entre os demais Macro Critérios, para obter a média geral do fornecedor.

Os dados de uma avaliação são os seguintes:

Macro Critério	Peso_Macro	Critério	Peso_Critério	Nota_IDF
Qualidade	0,25	Conformidade com a especificação/solicitação	0,4	8
Qualidade	0,25	Documentação associada	0,25	8
Qualidade	0,25	Capacidade de atendimento: recursos e processos	0,35	8
Entrega	0,25	Pontualidade	0,75	9
Relacionamento	0,175	Ética, Cordialidade, equilíbrio em situações de conflito	0,45	10
Relacionamento	0,175	Comunicação clara e transparente	0,2	8
Relacionamento	0,175	Comprometimento	0,35	9
Entrega	0,25	Documentação Fiscal de cobrança em conformidade	0,25	9
SMS - RS	0,15	Atendimento aos requisitos legais (PPRA, PCMSO, ASO)	0,4	10
SMS - RS	0,15	Responsabilidade solidária	0,5	N/A
SMS - RS	0,15	Iniciativas voltadas a sustensabilidade Social e Ambiental	0,1	10
Negociação	0,175	Proposta conforme escopo solicitado	0,4	10
Negociação	0,175	Agilidade no envio da proposta	0,3	8
Negociação	0,175	Flexibilidade nas negociações	0,3	8

Consegui fazer a ponderação tranquilamente quando a dimensão do gráfico é o "Macro Critério", utilizando a seguinte expressão:

=(sum(if((Nota_IDF='N/A'),'',Nota_IDF)*Peso_Critério))/(sum(if((Nota_IDF='N/A'),0,Peso_Critério)))

Porém preciso gerar um gráfico da Evolução da média do fornecedor por trimestre e não consigo fazer com que a ponderação do Peso do Critério aconteça antes do cálculo da média geral.

Tentei utilizar a expressão:

=sum((if((Nota_IDF='N/A'),'',Nota_IDF)*Peso_Critério*Peso_Macro))/(sum(if((Nota_IDF='N/A'),0,Peso_Critério*Peso_Macro)))

Porém ela retorna o valor 8,82 quando deveria ser 8,91 (8*0,25+9*0,25+9,3*0,175+10*0,15+8,8*0,175).

Alguma sugestão? Deveria "setar" a média por Macro Critério no código fonte, utilizando 'set analysis' ou algum outro comando?

Estou um pouco perdida... Se for necessário encaminhar o arquivo em QlikView me avisem que trato as informações confidenciais.

Encaminho a base compilada e a planilha de avaliação individual para auxiliar na análise das fórmulas.

Desde já muito obrigada.

[cesaraccardi]

Ola Vanessa,

Eu acredito que seja possivel resolver o problema usando a funcao AGGR(), esta funcao cria uma especie de tabela temporaria para fazer um calculo dentro de outro calculo. O que quero dizer neste caso e que voce pode usa-la para forcar a Ponderacao do Peso do Criterio antes do Calculo da Media Geral. Algo como:

avg(

aggr((sum(if((Nota_IDF='N/A'),'',Nota_IDF)*Peso_Critério))/(sum(if((Nota_IDF='N/A'),0,Peso_Critério))),[Macro Criterio])

)

Esta formula nao esta incluindo Peso_Macro mas acredito que com um ajuste seja possivel incluir.

Espero ter ajudado.

Cesar

[#3]

Bom dia Cesar,

Tentei utilizar exatamente a sua expressão para ver que resultado ela retornaria, mas ela não puxou dado nenhum:

A dimensão do gráfico é o Período, isso influencia?

Obrigada!

[cesaraccardi]

Ola Vanessa,

O campo destacado em vermelho neste caso seria a dimensao que voce utilizou em seu grafico de barras, experimente corrigi-lo e veja se os dados sao apresentados. Quanto a dimensao Periodo, nao tem problema. O calculo deve funcionar da mesma forma.

[#5]

Boa tarde Cesar,

Corrigi a dimensão, os dados aparecem, porém o valor que a expressão retorna ainda não é o correto.

Será que há outra forma de corrigir?

Obrigada!

[arthur_dom]

Ola veja o qvw anexo. Fiz com base no que você descreveu como problema, com uma ligeira alteração  no script para manter as formulas simples.

[#7]

Arthur, obrigada!

Conversei com um colega de trabalho e acho que este é o caminho para chegar ao resultado sim!

O único problema é que a expressão ainda não pondera os pesos do Macro Critério quando todos os critérios dele são N/A. Estou encaminhando a Base de Dados atualizada com dois casos em que todos os critérios de um Macro Critério não são aplicáveis. Para o fornecedor B, o IDF ponderado deveria ser:

(10*0,25 + 10*0,25 + 8,91* 0,175)/(0,25+0,25+0,175) = 9,7174, porém pela expressão o valor que encontramos é 6,56.

Tentei fazer um novo agrupamento por fornecedor (objeto em amarelo) para ponderar os Macro Critérios mas o valor que a expressão chega é mais diferente ainda, 15,43.

Segue em anexo seu qvw atualizado e a base de notas com o caso que comentei, creio que um ajuste na fórmula resolva o problema porém já tentei simular várias vezes e não consegui.

Desde já muito obrigada!!

[arthur_dom]

Opa!!

Beleza... vi sua solucao e esta bem interessante... estou meio corrido aqui  mas de maneira rapida fiz conta de padaria dividi a nota pelos pesos.... e deu certo

mas sinto que da para simplificar...

De uma olhada e me diga se era isso

[#9]

Arthur!!!

Aparentemente deu certo sim! Quando o fornecedor tem apenas uma avaliação os dados bateram, estou fazendo algumas simulações pra fornecedores que tiveram mais de uma avaliação e confirmar se também dá certo! Atualizei a base com um exemplo.

Uma pergunta: eu consigo "setar" esta média (IDF) para fazer gráficos de comparação entre fornecedores? É possível fazer com que este índice seja calculado independente do que está sendo selecionado, para todos os fornecedores?

Tentei fazer um gráfico para classificar as notas mas não consegui. Ele só apresenta o índice correto quando eu seleciono um fornecedor, se mais de um é selecionado ele refaz o cálculo e apresenta o novo indice ponderado para um fornecedor. Preciso criar gráficos apresentando as maiores e piores notas, e compara-las entre projetos, escopo, etc...

Vamos matar um leão de por vez... rs!

Obrigada!

[arthur_dom · Accepted solution]

faltou um detalhe na expressão no aggr.... fiz uma nova sheet de uma olhada