Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Bypass selection of Year ,Month in AGGR function with sum

I want to bypass selection of Year , Month and Quarter but due to Aggr its not happening.

I used this expression

sum({< Month=, Quarter=, Year=,Status={'Active'}>}aggr(Amount,ContractNo))

and also  aggr(sum({< Month=, Quarter=, Year=,Status={'Active'}>}Amount),ContractNo)

but both are not working.

I want that on selection of Year, month ,Quarter my data is not changed.

Any ideas how I can tackle this issue.

Thanks

Vimlesh Gupta

7 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this expressions.

sum({< Month=, Quarter=, Year=,Status={'Active'}>} aggr(Sum(Amount),ContractNo))

or

sum({< Month=, Quarter=, Year=,Status={'Active'}>} aggr({< Month=, Quarter=, Year=,Status={'Active'}>} Sum(Amount),ContractNo))

Regards,

Jagan.

Not applicable
Author

Dear jagan,

I have tried it but its aslo not working.

Thanks & Regards

Vimlesh Gupta

Not applicable
Author

Aggr needs to go within an aggregation function as well as have an aggregation within it to give you an answer.

Try something like this...

avg(aggr(sum({< Month=, Quarter=, Year=, Status={'Active'}>}Amount),ContractNo))

One thing worth noting is that if you are going to change the avg to a sum then you don't need the aggr at all just simply this...

sum({< Month=, Quarter=, Year=, Status={'Active'}>}Amount)

Not applicable
Author

Dear  Mat Fryer,

On selection data is being changed.Its also not working.

Thanks & Regards

Vimlesh Gupta

Not applicable
Author

Selections in other fields will still effect the answer, it will only ignore those in the Year, Month and Quarter fields.

Just thrown this little example together and it works perfectly as I would expect.

http://www.filedropper.com/aggrtest

Selections in the Year field are ignored for the "All Time" calculation. Selects for a single Account still have an effect though.

Not applicable
Author

Hi,

I checked but this function returns wrong values.

I am going to upload a test file in which i write down  what i want.

and also example data.

Thanks & Regards

Vimlesh Gupta

--

Not applicable
Author

I think I see what you are trying to do now. You are trying to get the sum of the first record for each ContractNo.

Your problem lies in that the dimension you are using for the table is different to that used within the Aggr. You can't break out of dimensionality with Agrr and thus why its not working. The most simple solution is to add a flag to the table like this:

if(ContractNo<>peek(ContractNo),1,0) AS Flag_FirstContractRecord

and then use the following expression in the chart:

sum({<Year=, Month=, Flag_FirstContractRecord={1}>} Amount)