13 Replies Latest reply: Sep 8, 2011 4:24 AM by Gabriel Vázquez

# 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:

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

Gabriel,

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

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

Hi,

File Attachment qvw.

Thanks

• ###### 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}>}

IF(FACTOR_CONVER_COMPRA=0,1,FACTOR_CONVER_COMPRA)

Regards,

Stefan

• ###### 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

• ###### Two prices, choose higher

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

• ###### 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.

• ###### Two prices, choose higher

Strange...

If I put the above expression

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

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

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.

Regards

• ###### Re: Two prices, choose higher

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

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.

• ###### Re: Two prices, choose higher

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

Hi

Thanks for your help Stefan, I'll keep trying

Regards