Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
Not applicable

Aggr() function in set analysis

Hi all,


I am using the aggr() function for the very first time and is getting a bit confused.


I have the following table:

Capture.PNG

As it can be seen from the table, the price for a part varies from one order to another (ie. from order data to another order date).


Basically I want to perform a GROUP BY on the part field to retrieve the price for each part where the Order date is the latest one. In short I want a straight table with just the blue records (Order IDs 1, 4, 6, 9).


Till now I managed to get the latest order for each part by using the following expression:

=Date(aggr( Max([Order Date]), [Part ID]))


However, I am finding it a problem in getting the corresponding price. I tried to used the aggr() expression above in the following way:

=only({< [Order Date]={'$(=Date(aggr( Max([Order Date]), [Part ID])))'}>} Price)


However this is not returning the desired result.

Can anyone suggest how this can be done?

Thanks!

1 Solution

Accepted Solutions

Re: Aggr() function in set analysis

Sample attached

Capture.PNG

NOTE: In future paste your data as text instead of image so that creating a sample is easier for the people who are trying to help.

Thanks,

Sunny

View solution in original post

8 Replies

Re: Aggr() function in set analysis

Try this expression:

1) Date(Max([Order Date]))

2) FirstSortedValue(Price, -[Order Date])

Dimensions:

1) [Order ID]

2) [Part ID]

3) [Part Desc]

UPDATE: I think you don't need [Order ID] as a dimension. If you need to know that information you can add it like an expression:

FirstSortedValue([Order ID], -[Order Date])

MVP
MVP

Re: Aggr() function in set analysis

Try

FirstSortedValue( Price, -[Order Date])

Re: Aggr() function in set analysis

Create Straight Table

Dimension:

OrderID

PartID

Part Description

Expression:

=FirstSortedValue(Price ,- Order Date)

Chanty4u
Esteemed Contributor III

Re: Aggr() function in set analysis

just try with this

firststoredvalue(price,-[order date])

Re: Aggr() function in set analysis

Sample attached

Capture.PNG

NOTE: In future paste your data as text instead of image so that creating a sample is easier for the people who are trying to help.

Thanks,

Sunny

View solution in original post

Not applicable

Re: Aggr() function in set analysis

Thanks all for your help it worked perfectly fine

Not applicable

Re: Aggr() function in set analysis

Thanks Sunny for providing the QVW with an explanation. You're right next time I will but the data as text rather than an image.

Thanks again.

Re: Aggr() function in set analysis

Great,

Please close the thread down by marking correct and helpful answers.

Thanks,

Sunny