Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Two prices, choose higher


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?

13 Replies
MVP
MVP

Two prices, choose higher

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

Not applicable

Re: Two prices, choose higher

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

Not applicable

Re: Two prices, choose higher

Hi,

File Attachment qvw.

Thanks

MVP
MVP

Re: Two prices, choose higher

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

Not applicable

Two prices, choose higher

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

MVP
MVP

Two prices, choose higher

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

Not applicable

Two prices, choose higher

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


MVP
MVP

Two prices, choose higher

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

Not applicable

Two prices, choose higher

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

Community Browser