topic Re: Dates in Set Analysis in QlikView

Dates in Set Analysis

Karl_Hart — Thu, 20 Nov 2014 13:24:25 GMT

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

Re: Dates in Set Analysis

mato32188 — Thu, 20 Nov 2014 13:30:46 GMT

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

Re: Dates in Set Analysis

Karl_Hart — Thu, 20 Nov 2014 13:42:18 GMT

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

Re: Dates in Set Analysis

rubenmarin — Thu, 20 Nov 2014 13:53:56 GMT

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.

Re: Dates in Set Analysis

jolivares — Thu, 20 Nov 2014 13:54:10 GMT

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.

Re: Dates in Set Analysis

Karl_Hart — Fri, 21 Nov 2014 08:21:43 GMT

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.

Re: Dates in Set Analysis

Karl_Hart — Fri, 21 Nov 2014 08:37:14 GMT

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.