FirstSortedValue

[MK_QSL]

For the enclosed QVW file, I want to have Customer Having Maximum and Minimum Number of Invoices... I can get the Customer name who has Maximum Number of Invoices by using FirstSortedValue function but when I use the same function to achieve Customer with Minimum Number of Invoices, I am not getting any data...

Let me know where I am wrong...

[ecolomer]

can't find it, but if it's any help, when you make a selection of values ​​if that works, unless there are two equal values ​​in both Max and Min

[simondachstr]

FirstSortedValue(Customer, Aggr(Count(DISTINCT InvoNo),Customer))

will return null because that's the first sorted value. Check your straight table, untick Suppress Zero-Values & Suppress Missing and it will make sense to you.

A workaround would be either

a) use FirstSortedValue(Customer, Aggr(Count(DISTINCT InvoNo),Customer),2)

or

b) Use Set analysis to exclude null e.g.

FirstSortedValue({<Customer=P({$})>} Customer, Aggr(Count(DISTINCT InvoNo),Customer))

[swuehl]

As far as I remember, the Aggr() advanced aggregation table inside your FirstSortedValue() function will consider a customer NULL with a zero distinct count, which is the minimum in the table. It might work forcing QV to only consider customers with a value, like attached.

[MK_QSL]

For Min and Max, there are no equal values....Customer A is having Minimum number of Invoices i.e. 53

Customer D is having Maximum Number of Invoices i.e. 85

[MK_QSL]

Hi Martin, Thanks for your reply.

Yes, I already tried with FirstSortedValue with 2 before posting and it's working but my confusion is why it's not working with FirstSortedValue with 1 (default).

I have tried... unticking Suppress Zero Values (Presentation Tab) and Suppress When Value is Null (Dimension Tab) but I can't see any customer having Zero Value invoices...

Let me have the QVW file if you find something which I am missing...

FirstSortedValue function, I have used many times for getting min and max but this time, it's giving me headache.

[MK_QSL]

Dear Stefen, Thanks for your reply... But I couldn't find any customer having Zero Count Invoices... I have already achieved the result using the expression you have provided but curious to know what is wrong with this data set which is forcing us to use Customer = {"*"} inside FirstSortedValue?

[MK_QSL]

One issue I found that this problem started only after I created Master Calendar for InvoDate.

Is there anything from Master Calendar is wrong which is forcing the FirstSortedValue to consider null?

[simenkg]

This is caused by the AGGR function. It will evaluate Count(Distinct InvoNo) for all possible values of Customer. In your case the data model creates a Missing value (Null()) with count 0. This will be your minimum value.

Anyway, to get around this you have to filter the Customer-dimension in the outer aggregation function like this:

FirstSortedValue({<Customer={"=Count(distinct InvoNo)>0"}>}Customer, Aggr(Count(DISTINCT InvoNo),Customer

This will give you A as the answer

[anbu1984]

Default is outer join in Qlikview which created null customers for dates present only in Master calendar table

Scroll to bottom of table box in attached qvw