Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
prasadcm
Contributor II

Rangesum with Aggr?

Hi ,

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

Balance is total of Amount

102.png

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

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

14 Replies

Re: Rangesum with Aggr?

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?

prasadcm
Contributor II

Re: Rangesum with Aggr?

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

MVP & Luminary
MVP & Luminary

Re: Rangesum with Aggr?

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


talk is cheap, supply exceeds demand

Re: Rangesum with Aggr?

Check the attached now

prasadcm
Contributor II

Re: Rangesum with Aggr?

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 !



prasadcm
Contributor II

Re: Rangesum with Aggr?

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?

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

AutoNumber(RowNo(), BankNameField) as Row

prasadcm
Contributor II

Re: Rangesum with Aggr?

Yea,i tried above thing.

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

Re: Rangesum with Aggr?

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