How can I get nested IF´s and minimum value

[#1]

Hi, everybody, I have this Expression problem:

I need that

if VENTAS is less than 50% then my ABC VENTA column is "AA"

if VENTAS is greater than 50% my ABC VENTA Column is "A"

if VENTAS is grater than 80% my ABC VENTA Column is "B"

If VENTAS is greater than 95% my ABC VENTA Column is "C"

I already made this condition, but the problem is that my customer is asking me that the first NO_PARTE allways must bee "AA" but if my first "NO_PARTE" is greater than 50% my ABC Column wont be "AA"

I pasted an example of this and I attached the APP.

Please I really need your help!

Thanks!!

[swuehl · Accepted solution]

Well, you can check for the first row with rowno(), like

if(RowNo()>1,

if(RangeSum(Above(SUM({$<AGRUPADOR={'01_VENTAS'}>}(DATA_AVER))/SUM({$<AGRUPADOR={'01_VENTAS'}>}total DATA_AVER) , 0, NoOfRows()))>=.95,'C',

if(RangeSum(Above(SUM({$<AGRUPADOR={'01_VENTAS'}>}(DATA_AVER))/SUM({$<AGRUPADOR={'01_VENTAS'}>}total DATA_AVER) , 0, NoOfRows()))>=0.80,'B',

if(RangeSum(Above(SUM({$<AGRUPADOR={'01_VENTAS'}>}(DATA_AVER))/SUM({$<AGRUPADOR={'01_VENTAS'}>}total DATA_AVER) , 0, NoOfRows()))>=.50,'A',

if(RangeSum(Above(SUM({$<AGRUPADOR={'01_VENTAS'}>}(DATA_AVER))/SUM({$<AGRUPADOR={'01_VENTAS'}>}total DATA_AVER) , 0, NoOfRows()))<=0.50,'AA'))))

,'AA')

Hope this helps,

Stefan

[#3]

Great!!! It Works Fine, Thanks a lot, I really Appreciate it!!

You are very Good!!

[#4]

Hi Thanks for your answer, you really helped me, I have

one last question, do you know how I could get the number

of "AA" , "A", "B", "C" if this is a set analysis

operation?

in other words, I need to get the table "Contador" becouse

this table has fields that I will not have. I exported

UTOPE table to excel and then loaded to get the table

"Contador".

I atached the app, If you have the time to help me I would

appreciate it so much!!

thanks!!

El Mon, 28 Nov 2011 18:13:32 -0500

swuehl <qliktech@sgaur.hosted.jivesoftware.com>

escribio:

swuehl

>created the discussion

"Re: How can I get nested IF´s and minimum value"

To view the discussion, visit:

>http://community.qlik.com/message/168542#168542

[swuehl]

First of all, I noticed that your rating ABC Venta is depending on the sort order of NO_PARTE (you see this easily if you just select a couple of NO_PARTE). Is this what you want? Reason is because you use above function in the expression.

[#6]

The table "UTOPE" is perfect,the problem is the table

"contador", I don´t have the fields "ABC VENTA" and "ABC

UTOPE", I deleted the fields in script becouse the

reallity is that I don´t have them, what I need is the

table "Contador" but with the values of the table "UOPE".

I attached the new app.

I hope to explained better,

Thanks I really appreciate yout time and help.

El Tue, 29 Nov 2011 13:48:01 -0500

swuehl <qliktech@sgaur.hosted.jivesoftware.com>

escribio:

swuehl

>created the discussion

"Re: How can I get nested IF´s and minimum value"

To view the discussion, visit:

>http://community.qlik.com/message/168935#168935

[swuehl]

To create those fields dynamically for the table Contador, I would need advanced aggregation. I can't control the sort order of the dimension used in the advanced aggregation (NO_PARTE). If your result of ABC_VENTA is depending on the sort order (that is what I see), the result of the table Contador using advanced aggregation is uncontrolled.

Maybe I am missing some other approach, but this is where I am stucked now. Is there a replacement for the above function in your ABC_VENTA expression? What is the business rule behind?

[#8]

Table "UTOPE" is perfect, the problem is that I need to

get the table "CONTADOR" but with the values of the table

"UTOPE", becouse the table "CONTADOR" is made with 2

fields I don´t have, "ABC VENTA" and "ABC UTOPE", this

fields were loaded with an excel table that I dont have,

so, I need to count how many "AA", "A", "B" and "C". I

have.

I attached the app with out the fields "ABC VENTA" and

"ABC UTOPE".

Thank you very much for your time and help I really

appreciate it.

El Tue, 29 Nov 2011 13:48:01 -0500

swuehl <qliktech@sgaur.hosted.jivesoftware.com>

escribio:

swuehl

>created the discussion

"Re: How can I get nested IF´s and minimum value"

To view the discussion, visit:

>http://community.qlik.com/message/168935#168935

[#9]

for this table "CONTADOR" no matter the sort order or if

is uncontroled, but, how could be the Aggregation

expresion?

[swuehl]

The dimension expression could maybe look like this (see attached).

I don't understand why you are not interested in a consistent rating for ABC_VENTA, but here you are.

Regards,

Stefan