topic Re: Set Analysis MAX(DATE) Not resolving in QlikView

Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 14:42:05 GMT

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

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 14:52:45 GMT

Phill,

Try FirstSortedValue(), maybe:

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

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 15:07:47 GMT

Thanks for your reply Michael.

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

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 15:10:14 GMT

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

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 15:23:08 GMT

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.

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 15:31:22 GMT

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...

Re: Set Analysis MAX(DATE) Not resolving

Thu, 16 Jan 2014 15:54:12 GMT

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

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 16:27:33 GMT

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)))

Re: Set Analysis MAX(DATE) Not resolving

Anonymous — Thu, 16 Jan 2014 16:41:37 GMT

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