Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
Thank you!