topic Re: Aggr() function in set analysis in QlikView

Aggr() function in set analysis

Tue, 29 Mar 2016 10:51:51 GMT

Hi all,

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

I have the following table:

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!

Re: Aggr() function in set analysis

sunny_talwar — Tue, 29 Mar 2016 10:56:23 GMT

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])

Re: Aggr() function in set analysis

swuehl — Tue, 29 Mar 2016 10:56:43 GMT

Try

FirstSortedValue( Price, -[Order Date])

Re: Aggr() function in set analysis

Kushal_Chawda — Tue, 29 Mar 2016 10:57:55 GMT

Create Straight Table

Dimension:

OrderID

PartID

Part Description

Expression:

=FirstSortedValue(Price ,- Order Date)

Re: Aggr() function in set analysis

Chanty4u — Tue, 29 Mar 2016 11:00:16 GMT

just try with this

firststoredvalue(price,-[order date])

Re: Aggr() function in set analysis

sunny_talwar — Tue, 29 Mar 2016 11:08:08 GMT

Sample attached

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

Re: Aggr() function in set analysis

Tue, 29 Mar 2016 12:50:01 GMT

Thanks all for your help it worked perfectly fine

Re: Aggr() function in set analysis

Tue, 29 Mar 2016 12:50:57 GMT

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

sunny_talwar — Tue, 29 Mar 2016 12:51:21 GMT

Great,

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

Thanks,

Sunny