Minumal value with certain criteria

[sculptorlv]

Hello!

The problem is...

I have the chart in my qlikview report:

Number	Name	Type	Q	Price
1913654	GAIDA SIA	C-AULIKA		80
1913654	GAIDA SIA	C-PHEDRA	1	77
1913654	GAIDA SIA	C-PHEDRA	3	50
1913654	GAIDA SIA	C-PHEDRA	4	45
1913654	GAIDA SIA	C-PHEDRA	5	0

I can make an agrregation function to get minimal value for each Type:

=Aggr(MAX(Price,),Type) - the result is:

Number	Name	Type	Q	Price	Aggre
1913654	GAIDA SIA	C-AULIKA		80	80
1913654	GAIDA SIA	C-PHEDRA	1	77	77
1913654	GAIDA SIA	C-PHEDRA	3	50	77
1913654	GAIDA SIA	C-PHEDRA	4	45	77
1913654	GAIDA SIA	C-PHEDRA	5	0	77

Now,

I need to get similar formula to get MAX value for TYPE, where Q <=3 The result should be:

Number	Name	Type	Q	Price	Aggre
1913654	GAIDA SIA	C-AULIKA		80	80
1913654	GAIDA SIA	C-PHEDRA	1	77	50
1913654	GAIDA SIA	C-PHEDRA	3	50	50
1913654	GAIDA SIA	C-PHEDRA	4	45	50
1913654	GAIDA SIA	C-PHEDRA	5	0	50

How can I make such function??

Thank you in advance!!

[sunny_talwar · Accepted solution]

How about this:

Max(TOTAL <Type> {<Q = {"<=3"}>} Price)

[sculptorlv]

didn't got it .. why Total?

Now I get error in calculated dimansions

My currnet function is:

=Max(TOTAL <FA_Class> {<Min_Quantity = {"<= 3"}>} Price)

[sunny_talwar]

You are doing this in the dimension or is this an expression?

[sculptorlv]

I use straitght table chart.

And I tried to do this in the dimension..

Tried in the expressin = '-'

[sunny_talwar]

Why not use it as an expression?

[sculptorlv]

Yes, I did it in expression after your previous comment! Thank you!

One more quastion... I have some valule not filled (= null()), but for my calculation this mean = 0.

How can I modify this eaquation:

=Max(TOTAL <FA_Class> {<Min_Quantity = {"<= 3"}>} Price)

so it calculate null() as =0

something like:

OR Min_Quantity = null()

[sunny_talwar]

Try this may be:

=Max(TOTAL <FA_Class> {<Min_Quantity = {"<= 3"}>+<Min_Quantity -= {'*'}>} Price)

[sculptorlv]

is it ok -= ?

Doesn't work

I need to get this 45 value also for C- Aulika

where can I read about this syntaxis?

[sunny_talwar]

Lets try one more time

=Max(TOTAL <FA_Class> {<Min_Quantity = {"<= 3"}> + <FA_Class = {"=Len(Trim(Min_Quantity)) ="}>} Price)

=Max(TOTAL <FA_Class> {<Min_Quantity = {"<= 3"}> + <FA_Class = {"=Len(Trim(Concat(DISTINCT Min_Quantity))) = 0"}>} Price)