Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Ankhi
Creator
Creator

Need Help with Expression

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?

LnumLnameActual AmountPurposeStart DateEnd DateLicenNoFlag
1031410314-Mailing337.21Penabled01/03/2021 00:0028/02/2022 00:0026722/0003Y
1031410314-Mailing5432.34Penabled01/03/2021 00:0028/02/2022 00:0065375Y
1031410314-Mailing234.32Penabled01/03/2021 00:0028/02/2022 00:0094443Y
1031410314-Mailing230.12Penabled01/03/2021 00:0028/01/2022 00:0094443 
1031410314-Mailing670.21Penabled01/03/2021 00:0028/01/2022 00:0065375 

 

Many Thanks in advance.

Regards

Ankhi

3 Replies
stevejoyce
Creator III
Creator III

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
Creator
Creator
Author

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
Creator III
Creator III

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)