Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Expression/Logic help please

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.

4 Replies
sunny_talwar

May be like this

FirstSortedValue(Expense, SubExpDate)

apthansh
Creator
Creator
Author

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

sunny_talwar

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

apthansh
Creator
Creator
Author

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..

     

IDstatusSub_IDStartExpire
441Active50511/22/20104/30/2017
441Active8455/1/20175/31/2021