Bypass selection of Year ,Month in AGGR function with sum

[#1]

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

[jagan]

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.

[#3]

Dear jagan,

I have tried it but its aslo not working.

Thanks & Regards

Vimlesh Gupta

[#4]

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)

[#5]

Dear  Mat Fryer,

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

Thanks & Regards

Vimlesh Gupta

[#6]

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.

[#7]

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

--

[#8]

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)