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

[#1]

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.

[swuehl]

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?

[#3]

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)

)

[swuehl · Accepted solution]

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.

[#5]

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

[paulwalker]

Hi Swe\uehl,

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

is it possible to convert into set expression???