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: 
Anonymous
Not applicable

Get rangesum to ignore date filter on bank balance running totals

Hi,

I created a bar chart showing running totals for daily accumulating bank balances of 5 different bank accounts where I can filter for one or more bank names and see their running totals per day using this expression:

rangesum(Above(Sum([incom])-Sum([outcom]),0,RowNo()))

As long as When i filter by bank the running totals show the correct balances but if i filter by date the running totals only take into consideration the picked dates , therefore showing incorrect bank balances.

how do i get the rangesum calculation to ignore any date selection so the running totals still show the correct daily accumulating balance, but still allow bank name selections?

I understand Set Analysis can be of help here but I am not able to find the right way to use it.

Thanks in advance

14 Replies
sunny_talwar

That was my initial response also and this is what the OP mentioned:

"Sunny - I didn't create the MM YYYY in data load editor because editing the script disables the data manager and I really want data manager to work for me as i tend to get lost in scripting .

And when i tried to add a calculated field in the data preparation stage of data loading, the option was greyed out:"

swuehl
MVP
MVP

I think the option to add a new field in data manager is greyed out due to the concatenation of two or more tables (that's a limitation according to the HELP).

But you should be able to set the field type of your date field to Date, this should create an auto generated calendar, including a YearMonth field.

(or create a master calendar based on date in a script tab that is not controlled by data manager and add all calculated calendar fields you like)

You should be able to use these calendar fields in your set analysis (see also Henric's post I've linked to in my prev post).

You can also do what Sunny suggested,  ignoring all selections by using set identifier 1 and then explictely setting selections in few fields like your bank field taken from default state selection.

(I've attached an adapted qvf so you can see how this works).

But it looks like your calculation could be put into the script part (well, probably not just using the data manager), basically you would sort your fact table by bank and date and then accumulate the amounts using Peek() and Rangesum(), resetting the accumulated value on bank value change.

There are some samples in the forum on how to do something like this.

swuehl
MVP
MVP

This sample QVF is highlighting the selected date range, using a color expression

=If(Count(date),LightBlue(),Lightblue(50))

Anonymous
Not applicable
Author

Thanks Sunny, tried this solution but reached an obstacle as the second field i use for filtering is my MMYYYY master item which can't be used in the expression as i realize now.

Could  you please help me incorporate my master item formula directly to the set analysis expression above for field2? i get lost in all the brackets and dollar signs when trying to add it myself

My master MMYYYY item is defined like this: date(Monthend(Date),'MM-YYYY')

thanks for all your help!

Anonymous
Not applicable
Author

Stefan Wühl wrote:

This sample QVF is highlighting the selected date range, using a color expression

=If(Count(date),LightBlue(),Lightblue(50))

Thanks Stefan! the highlighting works great! if only i could get the bar chart to scroll to the selected area too...but i think i am getting carried away with my wishes