Need Help with Expression

[Ankhi]

Hi All,

I need a help with the below query..

I have a data set like this

For each unique combination of ColA(Lnum),D(purpose) and G(LicenNo), I need a flag(last column) to be marked for  the lines with the max End Date.

Please can you help?

Lnum	Lname	Actual Amount	Purpose	Start Date	End Date	LicenNo	Flag
10314	10314-Mailing	337.21	Penabled	01/03/2021 00:00	28/02/2022 00:00	26722/0003	Y
10314	10314-Mailing	5432.34	Penabled	01/03/2021 00:00	28/02/2022 00:00	65375	Y
10314	10314-Mailing	234.32	Penabled	01/03/2021 00:00	28/02/2022 00:00	94443	Y
10314	10314-Mailing	230.12	Penabled	01/03/2021 00:00	28/01/2022 00:00	94443
10314	10314-Mailing	670.21	Penabled	01/03/2021 00:00	28/01/2022 00:00	65375

Many Thanks in advance.

Regards

Ankhi

[stevejoyce]

Do you want it calculated in load script or on-the-fly calculation in your object and taking into account user selections.

For object level calculation you should be able to do:

If([End Date] = max(total <Lnum, purpose, LicenNo > End Date), 'Y')

[Ankhi]

Hi Stevejoyce,

Thanks for getting back to me. 

I am trying to use the expression in the front end by adding a calculated dimension Flag with the expression provided by you. But it is saying Error in calculation in my straight table output.

Can you please check once?

Regards

Ankhi

[stevejoyce]

Maybe because i didn't wrap one of your fields in brackets, how about:

If([End Date] = max(total <Lnum, purpose, LicenNo > [End Date]), 'Y')

if you are using it as a calculated dimension and not expression (i assume so you can filter) then it would be:

aggr(

  If([End Date] = max(total <Lnum, purpose, LicenNo > [End Date]), 'Y')

, [End Date], Lnum, purpose, LicenNo)