topic Re: Expression/Logic help please in QlikView

Expression/Logic help please

apthansh — Wed, 25 Nov 2020 16:16:04 GMT

ID SubID SubExpDate Expense

1 AB 4/30/2016 5000

1 BC 4/30/2017 6000

Here one ID has multiple SubID's but I only want to calculate the Expense for ID where SubExpDate has the most recent date.

Here sum(Expense) should be 5000 and disregard the SubID BC.

Can we do that ?

Thank you very much.

Re: Expression/Logic help please

sunny_talwar — Thu, 06 Apr 2017 19:12:38 GMT

May be like this

FirstSortedValue(Expense, SubExpDate)

Re: Expression/Logic help please

apthansh — Thu, 06 Apr 2017 19:26:03 GMT

Thank you Sunny...but this is how my expression is

(Sum(if([Lease Type] = 'Our Subtenant',

Aggr((Max([Amount of Expense]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1)),%SubspaceKey,[Expense Type],%ExpenseKey))*-1))

(Sum(if([Lease Type] <> 'Our Subtenant',

Aggr((Max([Amount of Expense]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1)),%SubspaceKey,[Expense Type],%ExpenseKey))))

Re: Expression/Logic help please

sunny_talwar — Thu, 06 Apr 2017 19:30:14 GMT

I won't know and understand much just by looking at your expression... I would suggest you to share a sample

Re: Expression/Logic help please

apthansh — Thu, 06 Apr 2017 20:15:30 GMT

Agree...let me put it in another way...

Can I add a logic - if 1 id has Multiple SUB ID's and among those sub Id's if one expires in the next 12 months and other is Starting within the next 12 month can I pull the second record.Here i WANT TO PULL THE sUB iD 845.How can I put it in script level..

ID	status	Sub_ID	Start	Expire
441	Active	505	11/22/2010	4/30/2017
441	Active	845	5/1/2017	5/31/2021