Max Value on the basis of multiple fields in Aggr

[swatitomar]

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.9	169.55
28/06/2017	D10002770.9	241
28/06/2017	D10002770.9	248
20/07/2017	D10009970.9	15
21/07/2017	D10009970.9	12

Output

Link_Date	Link_Partno	Rate
28/06/2017	D10002770.9	248
21/07/2017	D10009970.9	12

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

[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

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]

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_Date	Link_Partno	Rate
28/06/2017	D10002770.9	248
21/07/2017	D10009970.9	12

[sunny_talwar · Accepted solution]

My bad, try this

Dimensions

Link_Date

Link_Partno

Expression

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

[swatitomar]

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]

Hey,

By applying the above logic getting this result

Link_Partno	Rate
D10002770.9	248

But I want below output

Link_Partno	Rate
D10002770.9	248
D10009970.9	12

[swatitomar]

Correct Answer:

In case of below result

Link_Partno	Rate
D10002770.9	248
D10009970.9	12

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