topic Re: Minimum Function Doubt in QlikView

Minimum Function Doubt

tamilarasu — Fri, 11 Sep 2015 09:50:37 GMT

Hi All,

I have used a min function in my chart in order to get minimum sales value for one customer. In adjacent column, I just want to show the order id. Say for example, the data looks like below

Customer Sales Price Order ID

A 10 1111

B 9 2222

C 11 1000

I want to get a minimum sales price (9 in this case) in a column (Expression) and the order ID (2222) for that sales price. Also, I tried to use first sorted value for the sales value but the same not working for Order ID. It just picking the 1000 for the sales price value 9. Kindly guide.

Kind Note: This is just a sample data.

Re: Minimum Function Doubt

sunny_talwar — Fri, 11 Sep 2015 09:53:37 GMT

Can you share the raw data from which you got the above result?

Re: Minimum Function Doubt

MK_QSL — Fri, 11 Sep 2015 09:53:38 GMT

=FirstSortedValue([Sales Price],[Sales Price])

=FirstSortedValue([Order ID],[Sales Price])

Re: Minimum Function Doubt

Gysbert_Wassenaar — Fri, 11 Sep 2015 09:56:47 GMT

Try using the FirstSortedValue function: FirstSortedValue( [Order ID], [Sales Price])

Re: Minimum Function Doubt

tamilarasu — Tue, 15 Sep 2015 04:23:35 GMT

Hello All,

Sorry for the late reply. I Just tried the above solutions and came across one scenario. I used the below expression but it seems taking incorrect value fr two columns.

If suppose the values like below,

SO	PR.KB	PR.Cust_Name	PR.Cust_Numb
1000	12	AA	1
1000	12	BB	2
1000	12	CC	3
2000	12	DD	4

I am getting correct value in the pivot chart. But total field seems wrong. I just want to show best of best value in the total (as summary).

Material	Best ZP	SO	PR.Cust_Name	PR.Cust_Numb
A1	12	1000	DD	4

Expression I used:

Expression for SO.

=if(SecondaryDimensionality()<>0,

FirstSortedValue({<PR.KR={'12'}>} Distinct BEST_ZP , PR.KB),

If([Best_ZP]<>'',

FirstSortedValue(Distinct {<PR.KR={'912'},PR.KB={">0,01"}>} BEST_ZP , PR.KB)

)

Similarly I just changed the dimensions for SO, cust name and number.

Guess, Something wrong in the sorting the values.

See below excel for better understanding.

sunindia: I am not allowed to post original data.

Re: Minimum Function Doubt

sunny_talwar — Tue, 15 Sep 2015 04:29:57 GMT

Can you try this:

=if(SecondaryDimensionality()<>0,

FirstSortedValue({<PR.KR={'12'}>} Distinct PR.Cust_Name , PR.Cust_Numb),

If([Best_ZP]<>'',

FirstSortedValue(Distinct {<PR.KR={'912'},PR.KB={">0,01"}>} PR.Cust_Name , PR.Cust_Numb)

)

Re: Minimum Function Doubt

tamilarasu — Tue, 15 Sep 2015 05:38:19 GMT

Customer name and customer number itself wrong for the above SO. I think the problem is SO consists of two or more same prices with different Customer name and customer number.

If I change sort weight for an expression, it affects other columns (Values changing) in my original data.

I just tried the same scenario in below qvw file but not working.