Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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
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)