Get rangesum to ignore date filter on bank balance running totals

[Anonymous]

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

[sunny_talwar]

May be this if you just want to ignore Date.

RangeSum(Above(Sum({<Date>}[incom])-Sum({<Date>}[outcom]), 0, RowNo()))

In case you want to ignore Date, Month, Year, you can try this

RangeSum(Above(Sum({<Date, Year, Month>}[incom])-Sum({<Date, Year, Month>}[outcom]), 0, RowNo()))

[Anonymous]

Thanks Sunny! I tried the first option and it worked great when I used Date field as a filter.

I have now set up a master item called MonthDate to filter my other sheet charts by months (Rather than by days). My MonthDate master item looks like this:

date(Monthend(Date),'MM-YYYY')

However,  I can't seem to replace  {<Date>} with {<MonthDate>} in the above rangesum formula. Is it possible to add master items to formulas (i don't see the master items on the drop down list as i start to type their names)?

how should i adjust my rangesum formula to ignore selections made on my MonthDate filter?

And another question if i may , now that the running totals remain correct, is it possible that although the calculation is made on all dates, the display will still highlight / move to the chosen dates?

for example if i have data from August to 2016 to Jan 2018 and I filter the sheet for September 2017, is it possible for the bar chart to highlight or move to show the dates in September17 rather than remain on the first dates  from August16?

(apologies for the newbie questions....gotta start somewhere )

[sunny_talwar]

I am very new to Qlik Sense and don't really know if master item can be ignored or not, but why don't you create MonthDate in the script?

Date(MonthEnd(Date), 'MM-YYYY') as MonthDate

and now you should be able to ignore MonthDate by adding it to your set analysis

For second question, I am not 100% sure what you looking to do, would you be able to provide a sample qvf to clarify what is your expectation?

[swuehl]

1) You can only use fields from your model in a set modifier on the left of the equal sign, not calculated dimensions or master items that are not just a simple field.

As far as I see, using your master item and making selections in it should basically select in Date field, so the set expression that clears Date should work with your master item, too.

2) You may add some more complexity to your chart to filter on the selected month or high light the bar, but it may be easier to use e.g. an AsOf table approach for your accumulation over time:

The As-Of Table

[Anonymous]

Stefan, Sunny - thanks for trying to help me. much appreciated!

I am (trying to) attach a QVF to explain my issue better.

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:

Stefan - I don't know why but setting the exclusion condition on the  "date" field does not work when filtering by MMYYYY. see the qvf file.

the as of table seems like a very good solution but i am afraid it's too complex for my capabilities at this point. i hope there's a simpler solution.

[Anonymous]

Here is the qvf file

[swuehl]

What about creating your YearMonth field in the data load script instead of creating a master item?

You should then be able to add this field to the set expression.

See also

Filtering a master dimension with set analysis?

[sunny_talwar]

I guess master items are not getting ignored in the chart's expression. The alternative would be to block all selections using 1 and then allow for selections in some fields. For instance you want to allow selections in this field -> [מסגרת - שם בנק-שם בנק] then you can writ this:

RangeSum(Above(Sum({1<[מסגרת - שם בנק-שם בנק] = $::[מסגרת - שם בנק-שם בנק]>}[incom])-Sum({1<[מסגרת - שם בנק-שם בנק] = $::[מסגרת - שם בנק-שם בנק]>}[outcom]), 0, RowNo()))

Similarily, keep adding other fields to into your expression

[sunny_talwar]

Not sure why the syntax god moved around, but the general idea is this

RangeSum(Above(Sum({1<Field1 = $::Field1, Field2 = $::Field2, Field3 = $::Field3>}[incom])-Sum({1<Field1 = $::Field1, Field2 = $::Field2, Field3 = $::Field3>}[outcom]), 0, RowNo()))

Assuming you have only 4 fields in your application where you can make selections. The expression will filter for only Field1, Field2, & Field3. Try this approach and see if this helps you out.

Best,

Sunny