Two prices, choose higher

[#1]

Hi everybody.

I have a problem with set analysis calculation. There is some articles that more than one purchase price and I need to do calculation higher price, but everything that attemp fails.

This is the correct expression for articles with a single price purchase:

SUM({<AÑO_HASTA={9999},CARGO_ABONO={0}>}(PRECIO_COMPRA-(PRECIO_COMPRA*DTO/100))*CANTIDAD / IF(CONVERSION=0,1,CONVERSION)

And this is to find the maximum price

SUM({<AÑO_HASTA={9999},CARGO_ABONO={0}>} AGGR(MAX(PRECIO_COMPRA),CODIGO_ARTICULO)-                                                                                     (AGGR(MAX(PRECIO_COMPRA),CODIGO_ARTICULO)*DTO/100) / IF(CONVERSION=0,1,CONVERSION)

Somebody can help me?

[swuehl]

Gabriel,

not sure if I can help you. What do you mean with "everything that attemp fails". Errors? No Results? wrong results?

Could you clarify what the relation between Precio_compra and codigo_articulo is (N:1, 1:1 etc.?)?

I noticed that the multiplicator CANTIDAD is missing on your second expression, is there a reason for that?

If you could provide a simple example application, I think that would make things easier, too.

Regards,

Stefan

[#3]

Hi Stefan

You see, the result is misleading since it returns the value of the item with your top price and not the sum of the items purchased.

Each item has a SKU that's the key.

The multiplier is missing because I forgot to put it.

Insert some xls so you can see the results

thanks

[#4]

Hi,

File Attachment qvw.

Thanks

[swuehl]

Gabriel,

I am still looking into your data.

I think you have aproblem with your CANTIDAD, in your first table, you get two rows with sum(CANTIDAD) is 2 on each row. I think it should be only one (looking at the records, e.g. field CANTIDAD with the selection from your example xls.)

In the second table, the sum(CANTIDAD) is not correctly taken into account either, I am still looking into that, too.

But you may try this as expression:

=SUM({<AÑO_HASTA={9999},CARGO_ABONO={0},IMPORTE_ALBARAN2={0}>}

(AGGR(MAX(PRECIO_COMPRA)*sum(CANTIDAD),CODIGO_ARTICULO)-AGGR(MAX(PRECIO_COMPRA)*sum(CANTIDAD),CODIGO_ARTICULO)*DTO1/100))/

        IF(FACTOR_CONVER_COMPRA=0,1,FACTOR_CONVER_COMPRA)

Regards,

Stefan

[#6]

That's because it has two purchase prices, if we compare the first frame and the second picture we see the difference, my intention is to score as precio.sum (quantity) is well is 2.

If you look at the third column of the second table that is the correct price, which should lead in the fourth column, you could leave it but I have to tell if it is a charge or credit and also if I have selected a simple box año_hasta = 9999 sales quantities vary

Regards

Gabriel

[swuehl]

Ok, I see. Have you tried the expression I suggested above?

[#8]

Yes, it returns the conversion factor, what I need is the result of set analysis divided by converter if it have.

[swuehl]

Strange...

If I put the above expression

=SUM({<AÑO_HASTA={9999},CARGO_ABONO={0},IMPORTE_ALBARAN2={0}>}

(AGGR(MAX(PRECIO_COMPRA)*sum(CANTIDAD),CODIGO_ARTICULO)-AGGR(MAX(PRECIO_COMPRA)*sum(CANTIDAD),CODIGO_ARTICULO)*DTO1/100))/

        IF(FACTOR_CONVER_COMPRA=0,1,FACTOR_CONVER_COMPRA)

into the second table chart as expression, I get the same results as in column 3 of the second table, and I use your set expression.

Shall I upload the file again?

Regards,

Stefan

[#10]

Sorry, the expression used was poorly drafted.

If the result is the same but does not distinguish between debit and credit, whencargo_abono = 0 the result should be 72930 as cargo_abono = 1 the result should be1756, total=74298.

No problem upload it again

Regards