Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
All,
I work in Housing and our tenants rent balance gets updated on a weekly basis. I want to find out the maximum balance for the rent account in a given month.
So we will have
Week Month Balance
1 Apr £50
2 Apr £100
3 Apr £150
4 Apr £0
So in this example. I want to see the answer of £150
Doing
max(aggr(sum([Rent Balance]), TenancyID, IndexDate))
seems to give me the correct answer if I select the month I want manually.
However, I am looking to see what the balance is 3 months after an assessment date (which differs for each Tenant) so I know I need to add some set analysis along the lines of
{<Month=(AddMonths([Assesment Date],3))>}
but it doesn't seem to work.
Can anyone help me out with the syntax?
Thanks,
Karl
Hi Karl,
Set analysis is calculated for the whole table, not row by row, you can set:
{<Month={'$(=Month(AddMonths(Max([Assesment Date]), 3)))'}>}
If this returns 'Apr', set analysis will apply as Month={'Apr'} for all the rows, no matter what [Assesment Date] has a particular row.
{<Month = {'$(=AddMonths( [Assesment Date], 3) ) '}>}
Thanks Martin, but that is just returning null values....
Hi Karl,
Set analysis is calculated for the whole table, not row by row, you can set:
{<Month={'$(=Month(AddMonths(Max([Assesment Date]), 3)))'}>}
If this returns 'Apr', set analysis will apply as Month={'Apr'} for all the rows, no matter what [Assesment Date] has a particular row.
The best way to do that is using the firstsortedvalue function.
firstsortedvalue([Rent Balance],-Aggr(Max(IndexDate), TenancyID))
I don't know if using the correct fields name, but see this functions to solve your problem.
Juan,
I dont think this given me the max value for a defined month. The issue is I want a variabel month value to be used in the set analysis.
Ruben,
Thanks for the info. I was hoping that you could apply set analysis on a row by row basis, but sadly you say I cant. I'll have to think of a work around.
Incidentally, the formula you gave above is doing as you say, giving the max value for a set month, (Jan 14 in this case) I just cant see where that month has come from. Nevermind, I need to look for a different solution.
Thanks for your help.