topic Re: Double Aggr() in expression in QlikView

Double Aggr() in expression

Thu, 18 Oct 2012 13:25:36 GMT

Hey everyone i have the need to create a chart that always show the total amount paid for a selected project/projects. Im having some troubles using 2 times the aggr() function since is not returning me what i expect

Here the expression im working on.

AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), PROJECT)

This does what i want keeps the chart always with the total amount paid for the project ignoring low-level selections. Problem is that i need to insert a second AGGR() to avoid wrong results caused by rateal payments and similar stuff

AGGR(sum(AGGR({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT, INVOICE_PAYMENT_ID)), PROJECT)

This one instead is giving me the right amounts but when i do a low level selection, like Invoice_number, changes the chart and shows only the payment for the selected invoice.

Cant figure out how to solve this issue any help piece of advice will be apreciated

Regards,

Juan Pedro

Re: Double Aggr() in expression

swuehl — Thu, 18 Oct 2012 13:28:56 GMT

Try

AGGR(

sum({1} AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), INVOICE_PAYMENT_ID))

, PROJECT)

Re: Double Aggr() in expression

Thu, 18 Oct 2012 13:53:43 GMT

Hey there ty for your anserw,

Your expression is half working keeps the chart invariated but the aggregation is not correct at some point since my results are falsed.

Will work on it a bit maybe i can fix it

regards

Juan Pedro

Re: Double Aggr() in expression

swuehl — Thu, 18 Oct 2012 14:04:38 GMT

Try to duplicate the inner set expression:

AGGR(

sum({1<PROJECT_ID= P ()>} AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), INVOICE_PAYMENT_ID))

, PROJECT)

Re: Double Aggr() in expression

Thu, 18 Oct 2012 14:24:53 GMT

Was my first idea aswell but nothing is changing. im really out of idea's on this one the single aggr() works more than fine but when combined something thats not suppose to happen happens.

Re: Double Aggr() in expression

swuehl — Thu, 18 Oct 2012 14:29:31 GMT

Maybe you need to use the NODISTINCT qualifier with the aggr() function (one or the other, or both), and depending in which context you are using the aggr() function, it might require an additional aggregation around the complete current expression.

sum(AGGR(

sum({1<PROJECT_ID= P ()>} AGGR(sum({1<PROJECT_ID= P ()>} PAYMENT_AMOUNT), INVOICE_PAYMENT_ID))

, PROJECT))

I'm also not sure if you really need to aggr() functions, one embedded into the other, but it's hard to say without knowing more about your data model and expression context.

Could you maybe upload a small sample that demonstrates your issue?

Re: Double Aggr() in expression

Thu, 18 Oct 2012 15:47:53 GMT

Hey, im not at work anymore so cant send you a sample right now.

I will try to describe the situation a bit better and tomorrow i will post a sample.

- In my report i have summary page

- The summary page contains.

- Invoices chart

- Payments chart

- total project cost (The Chart that is giving me problems.)

Invoices and Payments are returning right results and this is a fact.

Now what i want is:

- When a user selects a project the chart shows the total cost for the project

- A low level selection (like payment_number) wont affect the chart. This means i only want Project to be considered in this specific chart leaving apart all the others selections.

- Since i have different payments i have to use the aggr() function to make the results right.

I can say the expression is not working fine at the moment because the amount in payments and total project cost should be identical when no low level selection is made.

Hope it Helps

Regards,

Juan Pedro