topic Re: How can I get nested IF´s and minimum value in QlikView

How can I get nested IF´s and minimum value

Mon, 28 Nov 2011 22:49:39 GMT

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!!

How can I get nested IF´s and minimum value

swuehl — Mon, 28 Nov 2011 23:13:16 GMT

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

How can I get nested IF´s and minimum value

Mon, 28 Nov 2011 23:19:19 GMT

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

You are very Good!!

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

Tue, 29 Nov 2011 17:10:44 GMT

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

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

swuehl — Tue, 29 Nov 2011 18:47:59 GMT

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.

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

Tue, 29 Nov 2011 19:14:30 GMT

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

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

swuehl — Tue, 29 Nov 2011 19:22:47 GMT

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?

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

Tue, 29 Nov 2011 19:23:31 GMT

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

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

Tue, 29 Nov 2011 19:34:20 GMT

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

is uncontroled, but, how could be the Aggregation

expresion?

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

swuehl — Tue, 29 Nov 2011 19:53:29 GMT

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

How can I get nested IF´s and minimum value

Tue, 29 Nov 2011 22:01:22 GMT

Hi swuehl, I thought It was gonna be general but with the consistent for ABC VENTA, so, then is this possible? getting this table with consistent ABC VENTA?

How can I get nested IF´s and minimum value

swuehl — Tue, 29 Nov 2011 22:15:20 GMT

Your definition for a AA rating of ABC Venta looks like this, right?

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

I think the rangesum(above(...)) part is problematic, since it is depending on sort order. But maybe I just understand your rating. Could you explain what ABC Venta should tell us? How can I read it?

How can I get nested IF´s and minimum value

swuehl — Tue, 29 Nov 2011 22:37:56 GMT

Ok, I think I understand, you want to show a kind of pareto chart. I missed the full accumulation and the sort expression in the ABC_VENTA expression. So I think I understand why you need the rangesum, but still, this doesn't give me a clue how to handle your CONTADOR chart, since I can't influence the sorting of the aggr() dimensions I need to reproduce the ABC_VENTA in that chart as dimension.

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

Tue, 29 Nov 2011 22:40:21 GMT

Sure,

If the field "Ventas" is less or equal than 50% then will be "AA", also if the condition is not met the first row will automaticlly be "AA"

if the field "Ventas" is greater or equal than 50% then will be "A"

If the field "Ventas" is greater or equal than 80% then will be "B"

if tue field "Ventas" is greater or equal than 95% then will be "C"

the more lower % the more profit we will get.

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

Wed, 30 Nov 2011 00:15:49 GMT

Did you find out the way to solve it?

How can I get nested IF´s and minimum value

swuehl — Wed, 30 Nov 2011 23:10:31 GMT

Sorry, I haven't.

Maybe you want to post a new thread with this requirement, so more forum members might have a look at it.

Regards,

Stefan

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

Wed, 30 Nov 2011 23:49:17 GMT

Ok, that wuold be worth it, I appreciate your help, you helped me really much.

Thanks and have a nice one!!