topic FirstSortedValue. Group By. Any way to do all this in one expression? in QlikView

FirstSortedValue. Group By. Any way to do all this in one expression?

Fri, 20 Nov 2020 18:02:30 GMT

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate)as

FirstProductBought

from abc.csv group by Customer;

This is the example for FirstSortedValue in the QV reference manual. It groups by the data in the script!

Anyone know a way to use the FirstSortedValue function AND 'Group By' clause within the SAME expression of a chart?

I don't want to 'group by' in the script because i want a few expressions, each grouping by a different fieldname.

FirstSortedValue. Group By. Any way to do all this in one expression?

swuehl — Fri, 20 Jan 2012 15:16:16 GMT

Either use your 'group by' field as dimension in your chart, or as dimension to an advanced aggregation aggr().

Could you explain your setting and requirement a bit more?

FirstSortedValue. Group By. Any way to do all this in one expression?

Fri, 20 Jan 2012 16:16:00 GMT

Yea,

I wish to count the number of ID's, based on the Date and Status. I only want to consider the latest Date for each ID and count how many are good, and how many are bad.

Sample table:

ID, ProductType, AuditorName, Date, Status

-----------------------------------------------------------------------------------------------

1 AA CompanyX 1/1 Good

1 BB CompanyX 1/31 Bad

2 BB CompanyY 1/3 Good

3 CC CompanyX 1/5 Bad

3 CC CompanyX 2/1 Good

3 AA CompanyX 3/1 Good

3 AA CompanyX 3/2 Bad

In my chart, I want the dimension to be either ProductType or AuditorName (I will use two separte charts, or just put these two fields in a cyclic group to use as the dimension)

I need two expressions:

Count how many ID's were good,
count how many ID's were bad.

********************ProductType*************************

So if my dimension was Product Type, I wanna see the ID counts for each status, broken down by Product (disregarding AuditorName):

Product AA, ID 1 ==> Good (1/1)

ID 3 ==> Bad (3/2)

Product BB, ID 1 ==> Bad (1/31)

ID 2 ==> Good (1/3)

Product CC, ID 3 ==> Good (2/1)

Therefore, these would be the counts for each Product TYpe:

Status=Good STatus=Bad

AA 1 1

BB 1 1

CC 1 0

********************AuditorName*************************

If I choose AuditorName as my dimension, I would count the numbers of ID's in each status, broken down by AuditorName (disregarding Product Type):

CompanyX, ID 1 ==> Bad (1/31)

ID 3 ==> Bad (3/2)

CompanyY, ID 2 ==> Good (1/3)

The resulting count for each AuditorName:

STatus=Good Status=Bad

CompanyX 0 2

CompanyY 1 0

================================

What can I do? THis does not do the job when counting number of Good ID's:

count

(

{ <Date= {"=FirstSortedValue( Date, -Aggr(ProductType, ID) )"} > }

distinct if(Status='Good',ID)

)

Re: FirstSortedValue. Group By. Any way to do all this in one expression?

swuehl — Fri, 20 Jan 2012 16:48:51 GMT

I think you could use something like

=sum(aggr(if(FirstSortedValue(Status, -Date)='Good',1),ProductType, ID))

as expression (for the case of ProductType table dimension). Please see attached.

FirstSortedValue. Group By. Any way to do all this in one expression?

Fri, 20 Jan 2012 17:02:29 GMT

Hi Swuehel,

That's really awesome.

I also tried creating a cyclic group that included ProductType and AuditorName.

In your expression formula, I replaced ProductType with the name of the cyclic group and it seems to work well all together in one chart!!

Thank you!

-WC

Re: FirstSortedValue. Group By. Any way to do all this in one expression?

paulwalker — Tue, 03 Feb 2015 02:48:58 GMT

Hi Swe\uehl,

=sum(aggr(if(FirstSortedValue(Status, -Date)='Good',1),ProductType, ID))

is it possible to convert into set expression???