Value based on max date

[BI_Dev]

@sunny_talwar  Hi - I am working on a similar solution where I want to also look at another column and then look for max date.

ID	TYPE	DATE	AMOUNT
1	A	10/10/20	200
1	B	10/9/20	300
1	A	10/8/20	700
2	A	10/9/20	800
2	B	10/8/20	700
2	A	10/10/20	600

Here ID 1 should pick 200 + 300 ( Type A&B) and ID 2 should pick 600+700.

I tried Order by Type, but that did not work.

Thank you much.

[Kushal_Chawda]

@BI_Dev  try below

sum(aggr(if(DATE = max(total <ID,TYPE>DATE),AMOUNT),ID,TYPE,DATE))

[BI_Dev]

Hi Kush - I am trying to do in the script..I tried below but this will not consider the TYPE .

Table:

LOAD

    Date,

    Type,

    ID,

   Amount

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

NoConcatenate

LOAD *

Resident Table

Where ID <> Previous (ID)

Order By ID, Type,Date desc;

DROP Table Table;

[Kushal_Chawda · Accepted solution]

@BI_Dev  try below

Data:
LOAD ID, 
     TYPE, 
     DATE, 
     AMOUNT
FROM Table;

Inner Join(Data)
LOAD ID, 
     TYPE, 
     Date(max(DATE)) as DATE
Resident Data
Group by ID, 
     TYPE;

[BI_Dev]

Thank you..but This is giving some weird Amount..doesn't seem to be working.

[Kushal_Chawda]

@BI_Dev  Please share sample data with expected output. Based on what you have provided , it is giving correct results

[sunny_talwar]

@BI_Dev I agree with what @Kushal_Chawda has provided should work... If it is not giving you what you wanted, there might be something missing which we cannot see in the data provided... can you find what that is and share with us

[BI_Dev]

yes, my bad - I was missing the field.Thank you much 🙂

[EmpireDist]

Good Afternoon, I'm not sure if this is the correct place to ask this. But I have a straight table based on sales by customer. I want to only display the last 3 invoice dates for each item per customer.