Dates in Set Analysis

[Karl_Hart]

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

[mato32188]

{<Month = {'$(=AddMonths( [Assesment Date], 3) ) '}>}

[Karl_Hart]

Thanks Martin, but that is just returning null values....

[rubenmarin · Accepted solution]

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.

[jolivares]

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.

[Karl_Hart]

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.

[Karl_Hart]

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.