get max date value

[paulwalker]

Hi Community,

I have to show max date value in straight table - for below selection i have to show max Extract date, that should be 30/07/2019 and respective value BCD -2000. 

I have tried with  : SUM({<ADV_ExtractDate={'$(=Max(ADV_ExtractDate))'}>}Amount), but it's not working .

can you please help..

Thanks in Advance!

[paulwalker]

Apologies, may be I'm not explaining properly..

Sunny, have used, but showing few records..

Below screen, we have 10 ContactID's - these should be align to services..

So, my output should be..

AdviserName	LOE Issued	LOE Received	Write	Issued
Andrew Savage	-	2000	2000	-
Claire Keen	2000	-	-	-
Ian Waddell (Onshore)	5300	2550	-	-
Ken McCall	3400	3400	-	-
Kevin  Taylor (Onshore)	4800	-	-	-
Regina Tan	-	13000	5000	5000

Highlighted in Green already covered in LOE Received and Issued, it should not be in other services (Highlighted in red- not should be present).

Hope you understand!

[sunny_talwar]

Do you mean that if the value is the same it should not repeat in multiple Services? but otherwise it can? I am confused because why do we have Ian show up in multiple Services?

[paulwalker]

I'm sorry, Have not selected July. July should be below screen.

By default - data should be club.

Ian has 2 ContactId's (25531683, 27205242), but service level associating one ID(25531683).

Service has different mapping using with StatusID

AdviserName	LOE Issued	LOE Received	Write	Issued
Andrew Savage	-	2000	2000	-
Claire Keen	2000	-	-	-
Ian Waddell (Onshore)	2750	-	-	-
Ken McCall	3400	3400	-	-
Kevin  Taylor (Onshore)	4800	-	-	-
Regina Tan	-	13000	5000	5000

[sunny_talwar]

Yet again, why do we see LOE Received and Issued number for Regina Tan when we only need to have a single number for Service?

[paulwalker]

For Regin Tan has 2 records 6000 and 7000 = 13000

below screens...

Example 1: For ContactID =20346419 has two services, but we should take - maximum date of ADV_ExtractDate

it should consider LOE Received

Example2: for 20352236 has two services, but we should take - maximum date of ADV_ExtractDate

it should consider LOE Received

Example 3: for 26861834, Please note here - StatusID we have 2(16161 and 16185) - Service associating with one StatusID 

For Service list - 16185 we are not considering for Service, if we consider 13000 value could be in two service.

Each opportunity (Amount) will only appear once on this Report.

Hope it helps!...

[sunny_talwar]

Why is StatusId coming into play for 26861834 and not for 20346419?

I think we are having a tough time understanding what you are trying to get here and why?

[paulwalker]

StatusID should be for all the ContactID's, StatusID mapping for Services.

Just what i need, which is the maximum date of ADV_ExtractDate Amount.

Each opportunity(Amount) will only appear once in the table, it should not be appear in different levels.

[sunny_talwar · Accepted solution]

Can you try this

Sum(Aggr(If(ADV_ExtractDate = Max({<Service *= {"*"}>}Total <AdviserName, ContactID> ADV_ExtractDate), Sum(Amount)), AdviserName, Service, ADV_ExtractDate, %Key, ContactID))

[paulwalker]

Thanks Sunny, this is what i'm expecting!..

Have one more - If you could help me, much appreciated!

Cumulative Report:  Within the defined period, how much value has flowed through the system for July

Column1	LOE issued	LOE Received	Issued
Ian Waddell
Andy Savage		2000
Claire Keen
Regina Tan	13000	13000	5000

[sunny_talwar]

Please close this thread and open a new one for this.

Thank you,
Sunny