topic Re: Minumal value with certain criteria in QlikView

Minumal value with certain criteria

sculptorlv — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Minumal value with certain criteria

sunny_talwar — Thu, 28 Jul 2016 13:16:15 GMT

How about this:

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

Re: Minumal value with certain criteria

sculptorlv — Thu, 28 Jul 2016 13:23:19 GMT

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)

Re: Minumal value with certain criteria

sunny_talwar — Thu, 28 Jul 2016 13:28:46 GMT

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

Re: Minumal value with certain criteria

sculptorlv — Thu, 28 Jul 2016 13:35:15 GMT

I use straitght table chart.

And I tried to do this in the dimension..

Tried in the expressin = '-'

Re: Minumal value with certain criteria

sunny_talwar — Thu, 28 Jul 2016 13:36:32 GMT

Why not use it as an expression?

Re: Minumal value with certain criteria

sculptorlv — Thu, 28 Jul 2016 13:51:28 GMT

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()

Re: Minumal value with certain criteria

sunny_talwar — Thu, 28 Jul 2016 13:54:31 GMT

Try this may be:

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

Re: Minumal value with certain criteria

sculptorlv — Thu, 28 Jul 2016 14:05:30 GMT

is it ok -= ?

Doesn't work

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

where can I read about this syntaxis?

Re: Minumal value with certain criteria

sunny_talwar — Thu, 28 Jul 2016 14:10:04 GMT

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)

Re: Minumal value with certain criteria

sculptorlv — Thu, 28 Jul 2016 14:13:53 GMT

Really TNAK YOU!!

Something absolutely new for me ...

Re: Minumal value with certain criteria

sculptorlv — Thu, 28 Jul 2016 14:19:32 GMT

Uhh... I forgot.. this number 3 is calculated value!!

sum(IF(InMonth(Bouth__PostingDate,Today(),-1),Bouth_Cofee_Item_Quantity,0))

Is it possible to migrate it in previous formula

Re: Minumal value with certain criteria

sunny_talwar — Thu, 28 Jul 2016 14:24:31 GMT

Now you made it really complicated

Would it be possible to share a sample to check it out?

Preparing examples for Upload - Reduction and Data Scrambling