Qlik Community

Qlik Brasil

Group community for Brazil users. discussion only in Portuguese.

Highlighted
Not applicable

Calculo de Margem

Voltando aqui para pedir ajuda a comunidade.

Tenho duas tabelas, uma com as compras das loja e outra com as vendas das lojas, e queria uma luz em como calcular a margem dos produtos vendidos (Valor Venda - Valor Custo)/Valor Venda

As tabelas seriam assim

TABELA VENDA PARA AS LOJAS

DataNFDestinoProdutoQuantValor Unitario CustoValor Total Custo
26-Jan-1727019Loja 1BE047460,00240,00
26-Jan-1727019Loja 1CL001550,00250,00
26-Jan-1727019Loja 1BE023430,00120,00
26-Jan-1727019Loja 1CL212380,00240,00
26-Jan-1727019Loja 1CL012275,00150,00
26-Jan-1727019Loja 1ML012790,00630,00
01-Feb-1727095Loja 2BE047560,00300,00
01-Feb-1727095Loja 2CL00155,0025,00
01-Feb-1727095Loja 2CL012275,00150,00

TABELA VENDA DAS LOJAS

FilialData VendaNFProdutoQuantValor Unitario VendaValor Total Venda
Loja 209-mar-174454CL0121168,00 168,00
Loja 210-mar-174456BE0471128,00 128,00
Loja 210-mar-174456CL0011105,00 105,00
Loja 101-mar-1714704BE0471128,00 128,00
Loja 101-mar-1714704CL0011105,00 105,00
Loja 101-mar-1714704CL0121168,00 168,00
Loja 128-mar-1714825CL0014105,00 420,00
Loja 128-mar-1714825ML0124199,00 796,00

Se alguem puder ajudar agradeco.

Muito obrigado

Luis

4 Replies
laddu_927
Not applicable

Re: Calculo de Margem

Why not join both the tables and use normal expression (Value Sale - Value Cost) / Value Sale??

Not applicable

Re: Calculo de Margem

The tables are already linked by "product" (Produto)


The problem is I just want the margin from the produts sold.


To pick the "Value Sale" is easy, hard is to get the respective (same products and same quantities) "Value cost"


Can you help me?

laddu_927
Not applicable

Re: Calculo de Margem

When you join with product, you get the Value Sale of same products I didn't get you why you need same quantities??

Can you please explain what you are trying to calculate??

Not applicable

Re: Calculo de Margem

Shiva,

Tks for your help

Let me try to explain better

Another example

Cost Table     

ProductQuantValue CostTotal Value Cost
Prod 1010100,001.000,00
Prod 20575,00375,00

Sold Table   

ProductQuant SoldValue SaleTotal Value  Sale
Prod 103300,00 900,00
Prod 201150,00 150,00

The correct margin for the sold products is

Total Value Sale = 1.050 (3 x 300 + 1 x 150)

Total Value Cost = 375  (3 x 100 + 1 x 75)

Margin = 0,6429 or 64,29%

I tried to do that in 2 ways at quik sense and get different number as bellow

(Sum([Value Sale])-Sum([Value Cost]))/Sum([Value Sale])  -  I get 0,6111 or 61,11%

(Sum([Total Value  Sale])-Sum([Total Value Cost]))/Sum([Total Value  Sale])  -  I get  -0,3095  or  -30,95%

Hope you can help me. 

Cheers