topic Re: get max date value in QlikView

get max date value

paulwalker — Sat, 16 Nov 2024 01:48:56 GMT

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!

Re: get max date value

tresB — Mon, 02 Dec 2019 07:07:49 GMT

Your field name in set analysis needs to be corrected, may be:

SUM({<ADV_ExtractDate={'$(=Max(ADV_ExtractDate))'}>}Amount)

Re: get max date value

paulwalker — Mon, 02 Dec 2019 07:12:08 GMT

I'm sorry, have written incorrect.

Updated in main post

Re: get max date value

tresB — Mon, 02 Dec 2019 07:47:59 GMT

By correcting the field name, I could get the value. Could you explain a bit more about your expected output?

Re: get max date value

paulwalker — Mon, 02 Dec 2019 08:41:14 GMT

Thanks for looking it..

My output should be value should be in one level, if it is cover in ABC, should not be in another level.

Output attached in the Application.. (which i have highlight in red, should not be in)

AdviserName	Service	ABC	BCD	DEF	EFG
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

Re: get max date value

tresB — Mon, 02 Dec 2019 09:32:55 GMT

This?

FirstSortedValue(DISTINCT Aggr(Sum(Amount),AdviserName, Service), -ADV_ExtractDate)

Re: get max date value

paulwalker — Mon, 02 Dec 2019 11:28:47 GMT

Have tried this, for JULY and ContactID - value should be only for BCD

It should be Maximum of ADV_ExtractDate.

Re: get max date value

tresB — Mon, 02 Dec 2019 12:16:42 GMT

Try without Service in aggr(), like:

FirstSortedValue(DISTINCT Aggr( Sum(Amount),AdviserName), -ADV_ExtractDate)

Re: get max date value

paulwalker — Mon, 02 Dec 2019 12:50:47 GMT

No Luck!

Do you think any script issue ?

Re: get max date value

sunny_talwar — Mon, 02 Dec 2019 12:58:59 GMT

May be this

Sum(Aggr( If(ADV_ExtractDate = Max(Total <AdviserName> ADV_ExtractDate), Sum(Amount)) , AdviserName, Service, ADV_ExtractDate))

Re: get max date value

paulwalker — Mon, 02 Dec 2019 14:07:19 GMT

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!

Re: get max date value

sunny_talwar — Mon, 02 Dec 2019 14:12:48 GMT

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?

Re: get max date value

paulwalker — Mon, 02 Dec 2019 14:27:41 GMT

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

Re: get max date value

sunny_talwar — Mon, 02 Dec 2019 14:27:40 GMT

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?

Re: get max date value

paulwalker — Mon, 02 Dec 2019 14:53:42 GMT

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!...

Re: get max date value

sunny_talwar — Mon, 02 Dec 2019 15:19:45 GMT

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?

Re: get max date value

paulwalker — Mon, 02 Dec 2019 16:00:59 GMT

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.

Re: get max date value

sunny_talwar — Mon, 02 Dec 2019 16:18:03 GMT

Can you try this

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

Re: get max date value

paulwalker — Mon, 02 Dec 2019 17:45:18 GMT

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

Re: get max date value

sunny_talwar — Mon, 02 Dec 2019 18:42:29 GMT

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

Thank you,
Sunny