topic Re: Re: FirstSortedValue in QlikView

FirstSortedValue

MK_QSL — Thu, 30 Oct 2014 22:00:01 GMT

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

Re: FirstSortedValue

ecolomer — Thu, 30 Oct 2014 22:08:12 GMT

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

Re: FirstSortedValue

simondachstr — Thu, 30 Oct 2014 22:20:22 GMT

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)

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

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

Re: FirstSortedValue

swuehl — Thu, 30 Oct 2014 22:24:30 GMT

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.

Re: FirstSortedValue

MK_QSL — Fri, 31 Oct 2014 07:09:45 GMT

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

Re: FirstSortedValue

MK_QSL — Fri, 31 Oct 2014 07:15:07 GMT

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.

Re: FirstSortedValue

MK_QSL — Fri, 31 Oct 2014 07:17:00 GMT

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?

Re: FirstSortedValue

MK_QSL — Fri, 31 Oct 2014 07:54:52 GMT

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?

Re: FirstSortedValue

simenkg — Fri, 31 Oct 2014 09:23:45 GMT

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

Re: FirstSortedValue

anbu1984 — Fri, 31 Oct 2014 09:30:14 GMT

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

Re: Re: FirstSortedValue

simondachstr — Fri, 31 Oct 2014 09:33:08 GMT

Re: FirstSortedValue

MK_QSL — Fri, 31 Oct 2014 10:17:39 GMT

Thanks to all for your help....