topic Two prices, choose higher in QlikView

Two prices, choose higher

Tue, 06 Sep 2011 07:30:59 GMT

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?

Two prices, choose higher

swuehl — Tue, 06 Sep 2011 08:50:37 GMT

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

Re: Two prices, choose higher

Tue, 06 Sep 2011 09:52:40 GMT

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

Re: Two prices, choose higher

Tue, 06 Sep 2011 10:45:59 GMT

Hi,

File Attachment qvw.

Thanks

Re: Two prices, choose higher

swuehl — Tue, 06 Sep 2011 11:00:52 GMT

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

Two prices, choose higher

Tue, 06 Sep 2011 11:26:08 GMT

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

Two prices, choose higher

swuehl — Tue, 06 Sep 2011 12:07:06 GMT

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

Two prices, choose higher

Tue, 06 Sep 2011 12:20:13 GMT

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

Two prices, choose higher

swuehl — Tue, 06 Sep 2011 12:28:11 GMT

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

Two prices, choose higher

Tue, 06 Sep 2011 12:41:27 GMT

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

Re: Two prices, choose higher

swuehl — Tue, 06 Sep 2011 13:01:43 GMT

I have attached my version. I noticed that the results are not the same compared to the 3. column, depending on selections.

Regards,

Stefan

Re: Two prices, choose higher

Wed, 07 Sep 2011 08:18:16 GMT

Hi Stefan,

Forgive the late response, the results are incorrect, I encounter a problem in the expression, I think the set_analysis not work, if you look you will see that the table is selected ultima_tarifa 9999 and if you cancel that selection results vary set_analysis and should not because it is a condition for the set for the sum ..... I don´t think use sum(cantidad) is correct in set analysis.

I think I'll go crazy.

Thanks for your help

Re: Two prices, choose higher

swuehl — Wed, 07 Sep 2011 14:43:28 GMT

Hi Gabriel,

yes, that's looks a bit confusing now, I am also struggling. Though I still think that using sum(cantidad) in the aggr() functions should be ok, the results seems to look ok to me also on a per row base.

Regarding the set expression, I think you should consider putting the set expression in the sum as well as in the aggregation functions in the aggr() functions (max() and sum() there). Having this, your numbers will not change on selecting / deselecting ano_hasta 9999.

Since there are a lot more fields to select from (e.g. Mes and Dias), you might take care of the possible alternative selections the user can make, i.e. by clearing the selection state of those fields.

You might want to take a look here:

http://iqlik.wordpress.com/2010/11/27/the-magic-of-set-analysis-point-in-time-reporting/

Hope this helps,

Stefan

Two prices, choose higher

Thu, 08 Sep 2011 08:24:11 GMT

Thanks for your help Stefan, I'll keep trying

Regards