Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

sum of last available prices with set analysis and aggregation

Hello Guys.

I have a set of data with products, prices and dates of these prices.

I want to sum prices of all products, but only last available prices.

Could you help create a working expression for that?

PRODUCTPRICEDATE OF PRICE
A1112014-10-20
A1102014-10-21
A192014-10-22
A2122014-10-23
A2132014-10-24
A2142014-10-25

The result here should be 23: 9 (for product A1) + 14 (for product A2) = 23.

1 Solution

Accepted Solutions
Highlighted
Contributor III
Contributor III

Re: sum of last available prices with set analysis and aggregation

Try this:

=sum(aggr(if(max(TOTAL <PRODUCT> [DATE OF PRICE])=[DATE OF PRICE], sum(PRICE)), PRODUCT, [DATE OF PRICE]))

Capture.PNG.png

View solution in original post

3 Replies
Highlighted
Contributor III
Contributor III

Re: sum of last available prices with set analysis and aggregation

Try this:

=sum(aggr(if(max(TOTAL <PRODUCT> [DATE OF PRICE])=[DATE OF PRICE], sum(PRICE)), PRODUCT, [DATE OF PRICE]))

Capture.PNG.png

View solution in original post

Highlighted
Partner
Partner

Re: sum of last available prices with set analysis and aggregation

Hi,

you may resolve this with aggr and firstsortedvalue,

check the attached example,

regards

Highlighted
Contributor II
Contributor II

Re: sum of last available prices with set analysis and aggregation

I think you can use this:

FirstSortedValue (PRICE,-DATE_OF_PRICE) as LAST_PRICE,

The minus sign (before Date o f Price) is due to the fact that you want the "Last Value".

There is not a "LastSortedValue" function.

Regards

Gustavo