How do I show the last balance on the last day of the month where the balance occurse more than once?

[andy_smith2005]

Hi,

I have a monthly spreadsheet which I download from my bank to show Date, Transactions, Money In, Money Out and Balance.

I want to be able to display a Straight Table showing the Last day of the month and the balance on that day. The main issue I have is that because there are more than one transactions on the last day of the month there are multiple Balances and I only want the Minimum Balance within that group.

I have tried

• Dimension of MonthEnd(Date) and Expression of Only  (Balance) ... that didn't work - which I expected
• Dimension of MonthEnd(Date) and Expression of Min (Balance) ... this gives me the minimum balance within the month and not on the last day of the month

I have tried some weird and wonderful Set Analysis expressions which give me some amazing results, none of which are correct including using max(RecNo())

Anyone got any ideas??

[ramoncova06]

you can use aggr for this

QlikView Technical Brief - AGGR.docx

the expression would be something like this, it really depends on you data set

min(aggr(avg({<Date = {"$(=max(Date))"}<}Balance),Month))

[andy_smith2005]

Ramos - thanks,

I tried the following and it only partially works. If I create a straight table it will only display one record regardless of the filter or dimension.

Any ideas?

min({<Date = {"$(=max(Date))"}>}Balance)

[Anonymous]

Maybe:

Min({< Date = {<Date = {"=MonthEnd(Date)"}>} Balance)

[andy_smith2005]

Neetha  - thanks - no that didn't work either - still gives only one record

[ramoncova06]

Andy, could you upload an example ?

I just want to be sure I understand how and what you want to display