Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
swatitomar
Contributor III
Contributor III

Max Value on the basis of multiple fields in Aggr

 

Dear All,

Facing issue in finding the Latest/max Rate on the basis of  Part_no and Link_Date but dere are two scenario that I have to check.

1. Pick Rate on the basis of Max Link_Date of any part_no.

2. If Link-Date is same for any Part_no den pick the max Rate

Input:

Link_Date Link_Partno Rate

28/06/2017

D10002770.9169.55
28/06/2017 D10002770.9 241
28/06/2017 D10002770.9 248
20/07/2017D10009970.915
21/07/2017D10009970.912

 


Output


Link_DateLink_PartnoRate

28/06/2017

D10002770.9248
21/07/2017D10009970.912

I Tried:   FirstSortedValue(Rate,-aggr(max(Rate),Link_Partno,Rate)))

Issue is : not showing the data if multiple Rate is there against single Link_date  of any part no .

Thanks in Advance

Regards

Swati

1 Solution

Accepted Solutions
sunny_talwar

My bad, try this

Dimensions

Link_Date

Link_Partno

Expression

If(Link_Date = Max(TOTAL <Link_Partno> Link_Date), Max(Rate))

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

Why not just this

Max(Aggr(Rate, Link_Partno))

sunny_talwar

In fact if you have Link_Partno as your dimension, you should be able to use just Max(Rate)

sunny_talwar

Check this out

Capture.PNG

Dimension

Link_Partno

Expression

=Aggr(If(Rate = Max(TOTAL <Link_Partno> Rate), Link_Date), Link_Date, Link_Partno, Rate)

Max(Rate)

Kushal_Chawda

Data:

LOAD Part_no ,

            Link_Date,

            Rate

FROM Table;


left join(Data)

LOAD Part_no ,

            Link_Date,

            max(Rate) as Rate,

            1 as MaxRateFlag

resident Data

group by

             Part_no ,

            Link_Date;



Now write expression


= only({<MaxRateFlag={1}>}Rate)


swatitomar
Contributor III
Contributor III
Author

Hey Hi,

I want the below result:

I have to check Rate on two different basis

first Pick Rate on the basis of Max( Link_Date) for part_no. and if Link-Date is same for any Part_no den pick the max Rate of that link_date


Output


Link_DateLink_PartnoRate

28/06/2017

D10002770.9248
21/07/2017D10009970.912
sunny_talwar

My bad, try this

Dimensions

Link_Date

Link_Partno

Expression

If(Link_Date = Max(TOTAL <Link_Partno> Link_Date), Max(Rate))

Capture.PNG

swatitomar
Contributor III
Contributor III
Author

Hey,

Thanks for the correct solution but now I don't want link_date in chart , it should show part and Rate only.

If I will not take link date in the chart then 0 is coming.

Is there any way?

swatitomar
Contributor III
Contributor III
Author

Hey,

By applying the above logic getting this result

Link_PartnoRate
D10002770.9248

But I want below output


Link_PartnoRate
D10002770.9248
D10009970.912

swatitomar
Contributor III
Contributor III
Author

Correct Answer:

In case of below result

Link_PartnoRate
D10002770.9248
D10009970.912

 

                 

FirstSortedValue(Rate,-aggr(max({< Link_Date={"$(=max(Link_Date))"}>}Rate),Link_Partno,Rate))