Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

phill_gilchrist
New Contributor III

Set Analysis MAX(DATE) Not resolving

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

Tags (4)
1 Solution

Accepted Solutions
jdvermeire
Contributor

Re: Set Analysis MAX(DATE) Not resolving

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

8 Replies
mov
Esteemed Contributor III

Re: Set Analysis MAX(DATE) Not resolving

Phill,

Try FirstSortedValue(), maybe:

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

phill_gilchrist
New Contributor III

Re: Set Analysis MAX(DATE) Not resolving

Thanks for your reply Michael.

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

mov
Esteemed Contributor III

Re: Set Analysis MAX(DATE) Not resolving

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

phill_gilchrist
New Contributor III

Re: Set Analysis MAX(DATE) Not resolving

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.

mov
Esteemed Contributor III

Re: Set Analysis MAX(DATE) Not resolving

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

Not applicable

Re: Set Analysis MAX(DATE) Not resolving

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

jdvermeire
Contributor

Re: Set Analysis MAX(DATE) Not resolving

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

mov
Esteemed Contributor III

Re: Set Analysis MAX(DATE) Not resolving

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

Community Browser