topic Re: Rangesum with Aggr? in QlikView

Rangesum with Aggr?

prasadcm — Wed, 18 May 2016 07:54:25 GMT

Hi ,

I have straight with expressions as Amount =Sum(Amount),Balance=RangeSum(above(total(sum(Amount)),0,rowno(Total)))

Balance is total of Amount

Now when i search for bank name eg: Bank Indosuez singapore

its amount is 102000 & balance is 102000 which is correct

But when i dont make any selections and view the table I get

Above bank Balance gets added

I want the balance to be grouped by bank name

I tried with aggr(RangeSum(above(total(sum(Amount)),0,rowno(Total))),[Bank Name]) bt didn't get desired output...

stalwar1‌

Re: Rangesum with Aggr?

sunny_talwar — Wed, 18 May 2016 08:07:30 GMT

Are you using QV12? If you are then this might work using this approach The sortable Aggr function is finally here!‌. Else for Aggr to work properly you will have to sort your data properly on the back end.

So are you using QV12? Can you provide a sample?

Re: Rangesum with Aggr?

prasadcm — Wed, 18 May 2016 08:17:02 GMT

Hi,

I am using v11.20.

I am providing sample data which is different than above mentioned example.

in the example i have tried to group by quarter

Regards,

Prasad

Re: Rangesum with Aggr?

Gysbert_Wassenaar — Wed, 18 May 2016 08:19:24 GMT

I think you're going to need an AsOf table for this so you can aggregate the amounts over the Posting Date per bank. See this document: Calculating rolling n-period totals, averages or other aggregations

Re: Rangesum with Aggr?

sunny_talwar — Wed, 18 May 2016 08:22:46 GMT

Check the attached now

Re: Rangesum with Aggr?

prasadcm — Thu, 19 May 2016 04:00:17 GMT

Hi Gysbert,

I went through ur link,I m finding it difficult to implement it in my app.

I created AsOf table & linked it to my table thorugh PostingDate i.e Period1

I have added expression to Balance as: sum({<PeriodType={'Rolling 3'}>}Amount).

I have attached qvw,can u let me know where im going wrong.

Changes are made in 'Copy of Bank Balance Report' tab.

Regards,

Prasad !

Re: Rangesum with Aggr?

prasadcm — Thu, 19 May 2016 08:00:35 GMT

Hi stalwar1,

Above example works correctly,how am i supposed to do it in my application?

U have mentioned AutoNumber(RowNo(), quarter) as Row

I added the above like AutoNumber(RowNo(),[Posting Date]) as Row

I am getting 0 in my result.

Can u help me out...I have added qvw in AsofTable

Regards,

Prasad

Re: Rangesum with Aggr?

sunny_talwar — Thu, 19 May 2016 09:34:40 GMT

You mentioned wanting to accumulate on Bank right? Try using this

AutoNumber(RowNo(), BankNameField) as Row

Re: Rangesum with Aggr?

prasadcm — Thu, 19 May 2016 09:48:25 GMT

Yea,i tried above thing.

I m getting 0 where i have mentioned my expression i.e Balance.

Re: Rangesum with Aggr?

sunny_talwar — Thu, 19 May 2016 09:49:57 GMT

Did you fix the sorting in a resident load before you use AutoNumber()?

Re: Rangesum with Aggr?

prasadcm — Thu, 19 May 2016 09:55:24 GMT

No,How am i supposed to do that?

Re: Rangesum with Aggr?

sunny_talwar — Thu, 19 May 2016 09:57:19 GMT

I will take a look at the attached sample you have posted on the other thread and give you suggestion

Re: Rangesum with Aggr?

prasadcm — Fri, 20 May 2016 11:20:04 GMT

Hi stalwar1‌

Solution suggested by you works,but i am getting duplicate records for it.

Regards,

Prasad

Re: Rangesum with Aggr?

sunny_talwar — Fri, 20 May 2016 11:50:15 GMT

Did it? I am still not fully sure what the expected output here is? Would be able to elaborate where you see the issue. May be take snapshots and circle the issue for us?

Thanks,

Sunny

Re: Rangesum with Aggr?

prasadcm — Fri, 20 May 2016 11:58:08 GMT

Hi stalwar1‌,