Price based on Min and Max date group by Type and Product

[v_jaideep]

Hi,

How do I find the Price based on Min and Max date which should be group by type and product.

I have a sample data below:

FROM:

Type	Price	Start date	Product
A	100	1/20/2021	ABC
A	111	12/10/2019	ABC
A	56	1/11/2019	ABC
B	23	3/20/2021	ABC
B	211	1/3/2020	ABC
B	45	12/25/2019	ABC
B	89	1/11/2019	ABC
C	21	4/13/2021	ABC
C	55	5/31/2020	ABC
C	87	1/11/2019	ABC

TO:

Type	Current	Old	Product
A	100	56	ABC
B	23	89	ABC
C	21	87	ABC

I have used temp and temp1 tables to get min and max start date in the script which are group by type and product but this is taking long time to run ....Is there any other work around to get  the expected data? Can we use firstsortedvalue() for multiple columns in the syntax for ex: firstsortedvalue(distinct price,-start date,Type)

[Dalton_Ruer]

=sum(Price*if([Start Date]=aggr(nodistinct max([Start Date]), Type),1,0))

I got it working with that using this post as the guide: https://community.qlik.com/t5/QlikView-App-Dev/Sum-Values-Having-Max-Date/m-p/405311

[v_jaideep]

@Dalton_Ruer This does not get me desired result. I wouldn't need sum of all the values for the particular type. I need value of only the max and min date when group by Type.

[tresesco · Accepted solution]

May be like:

FirstSortedValue( total <Type> Price, -[Start date])