Set Analysis MAX(DATE) Not resolving

[Anonymous]

I'm trying to do a simple sum for a list of matters.

I want 2 values per matter.

1, the last Bill Date which is working fine with:

=DATE(AGGR(MAX(BILL_DATE), MATTER_ID))

2, The last Bill value, which i thought doing BILL_DATE = the above would work, but isn't.

MONEY(AGGR(SUM({$<BILL_DATE = {"=$(=NUM(AGGR(MAX(BILL_DATE), MATTER_ID)))"}>}BILLED_AMOUNT), MATTER_ID))

Any help, greatly appreciated.

Thanks

[Anonymous]

Phill,

Try FirstSortedValue(), maybe:

MONEY(AGGR(FirstSortedValue(BILLED_AMOUNT, -BILL_DATE),MATTER_ID))

[Anonymous]

Thanks for your reply Michael.

I've tried applying your suggestion and it's returning null.

[Anonymous]

That's why I used "maybe"...  Can you upload a small example of your application?

[Anonymous]

Unfortunatly not.

Not being awkward, just wouldn't be feasible to do.

If it can't be done with out, so be it.

Appreciated none the less.

[Anonymous]

Last "blind" attempt.  Based on the fact you use sum(), I can assume that you have multiple BILLED_AMOUNT values in each date.  So:
MONEY(AGGR(SUM(if(BILL_DATE=DATE(AGGR(MAX(BILL_DATE), MATTER_ID)), BILLED_AMOUNT)), BILLED_AMOUNT))

It may depend on factors that I don't know.  Try to figure out from here...

[#7]

Hey Phill,

Please check out the attached file and see if it meets your requirement.

I put two text boxes. One of them calculates the max date for each MatterId and the other sums the BilledAmount corresponding to those max dates for each ID.

Hope this helps.

Thanks

AJ

[Anonymous · Accepted solution]

Hi Phill,

I understand what you're trying to do, as I have tried the same type of thing many times.  I think there is a general misunderstanding as to how set analysis functions in QlikView.  In general, SA seems to act like a sort of "where" clause for an expression, and therefore limits what is returned based on the SA expression.  In most cases this works for simple cases, but one thing to realize is that a SA expression evaluates at the environment level, not the row level.  The other major point to realize is that set analysis functions like a static user selection.  My suggestion is to try to think of SA in those terms: if you can do it with a selection, then it should work in a set expression.

On to your question:

I heavily recommend that you get max BILL_DATE and join it to the table containing MATTER_ID in the load script.  A second part of that is to create a 1/0 flag if the BILL_DATE = MAX_BILL_DATE, again, in the load script.  That way your UI expression becomes very simple:

Money(Sum({<MAX_BILL_DATE_FLAG={1}>} BILLED_AMOUNT))

Hope this helps and let me know if you need any clarification.

EDIT:

I just noticed one other thing.  For your expression, you need another aggregate function on the outside of Aggr()

Money(SUM(Aggr(Sum(...),MATTER_ID)))

[Anonymous]

Quite a practical explanation of the set analysis: "set analysis functions like a static user selection".