topic Re: Need Help with Expression in QlikView

Need Help with Expression

Ankhi — Wed, 18 Aug 2021 15:17:45 GMT

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

Re: Need Help with Expression

stevejoyce — Wed, 18 Aug 2021 15:27:10 GMT

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')

Re: Need Help with Expression

Ankhi — Thu, 19 Aug 2021 09:32:21 GMT

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

Re: Need Help with Expression

stevejoyce — Thu, 19 Aug 2021 10:12:04 GMT

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)