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: 
alec1982
Specialist II
Specialist II

calculate most recent paid amount from expense schedule

Hi guys,

I have a table with values: Key, Payment Date, Payment Type, Amount

I need to show in a straight table the most recent amount paid for each key before today's date where the expense type = rent and on another column where expense type not equal rent

I have attached a sample of the data and hope to get some help..

Thxs

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Hi Alec1982,

I think you can achieve this with a straigt table with your key as dimension and expressions like

=FirstSortedValue(

  {<[Payment Date] = {"<$(=today())"}, [Expense Type] = {'RENT'}>}

  [Expense Amount in GBP],

  -[Payment Date]

  )

and

FirstSortedValue(

  {<[Payment Date] = {"<$(=today())"}, [Expense Type] -= {'RENT'}>}

  aggr(sum({<[Expense Type] -= {'RENT'}>}[Expense Amount in GBP]),%LeaseKey, [Payment Date]),

  aggr(-only([Payment Date]), %LeaseKey,[Payment Date])

  )

On dates with multiple entries, you need to aggregate the values, right? (So you might need the aggr() function style solution also for RENT value).

See also attached.

View solution in original post

2 Replies
swuehl
MVP
MVP

Hi Alec1982,

I think you can achieve this with a straigt table with your key as dimension and expressions like

=FirstSortedValue(

  {<[Payment Date] = {"<$(=today())"}, [Expense Type] = {'RENT'}>}

  [Expense Amount in GBP],

  -[Payment Date]

  )

and

FirstSortedValue(

  {<[Payment Date] = {"<$(=today())"}, [Expense Type] -= {'RENT'}>}

  aggr(sum({<[Expense Type] -= {'RENT'}>}[Expense Amount in GBP]),%LeaseKey, [Payment Date]),

  aggr(-only([Payment Date]), %LeaseKey,[Payment Date])

  )

On dates with multiple entries, you need to aggregate the values, right? (So you might need the aggr() function style solution also for RENT value).

See also attached.

alec1982
Specialist II
Specialist II
Author

Thank you!