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

how to ignore date filter in a cashflow rangesum expression?

Hi,

I used the below expression to create a cash flow accumulating balance bar chart.

rangesum (above(Sum({<Date>}income)-Sum({<Date>}expense),0,RowNo()))

Below is a snapshot of what the result looks like. It works perfectly as long as i don't filter on the date. How do I lock the date filter and leave the option to filter on other fields  (the data is taken from 3 different banks, i want to be able to filter on each one and get the correct accumulating balance). I am sure it is possible with Set Analysis but i can't get the syntax right.

thanks in advance for your help.

11 Replies
ssutariya
Partner - Contributor
Partner - Contributor

Hi Anat,

You may be asking for ignoring the date field filter. Just add equal (=) sign after the date field in set expression. It may help. Below is the expression.

=rangesum (above(Sum({<Date=>}income)-Sum({<Date=>}expense),0,RowNo()))

dineshraj
Partner - Creator
Partner - Creator

Hi Anat,

I suggest you to find RangeSum in Load script.

Refer the below link

How to range sum values?

Rangesum aggregation script

jonathandienst
Partner - Champion III
Partner - Champion III

The RangeSum(Above()) construct only accumulates what is in the table. You may need to move the accumulation to the load script, or add a calculation of an opening balance for the selected dates. Something like:

     =RangeSum(Above(.....)) + <expression for balance on minimum date>

     or

     =RangeSum(Above(.....)) + <summed accumulation prior to minimum date>


Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Anonymous
Not applicable
Author

Thanks everyone. I'd rather try to ignore the date filter directly in the app if I can rather than adding the calculation to the script if possible

Suhas - Yep, I am trying to ignore the date filter, thanks for wording it better than my ambiguous title (i will try to edit it accordingly ):-).

I added the "=" after the date but it did not help. isn't there a set analysis syntax I can add to do this? something with {1} for the date field placed somewhere in the expression?

Many thanks for your help,

Anat

sunny_talwar

Try this:

RangeSum(Above(Sum({<Date>}income)-Sum({<Date>}expense), 0, RowNo())) * Avg(1)

Anonymous
Not applicable
Author

Hi,

Please find the expression

rangesum (above(Sum({<Date,[Periods] ={*}>}SumOfBudget_Amount),0,RowNo()))

Periods is my date filter and the chart doesn't change even though I select a value from periods.

The idea is to add your  datefield in the set analysis and {*} as above for my Periods field

rogerioqv
Creator II
Creator II

Try this: rangesum (above (Sum ({1} income) -Sum ({1} expense), 0, RowNo ())).

If you need to take any filter into account, use:

(Above (Sum ({1 <filter>} income) -Sum ({1 <filter>} expense), 0, RowNo ())).

Anonymous
Not applicable
Author

Thanks Rogerio! i am alsmost there.

When i use your first expression:

rangesum (above (Sum ({1} income) -Sum ({1} expense), 0, RowNo ()))

It does ignore all filters.

Since I want to allow bankname filter (there are 3 banks to choose from) I tried the below, adding the bankname field as the filter:

rangesum (above (Sum ({1<[bank name]={"*"}>} income) -Sum ({1<[bank name]={"*"}>} expense), 0, RowNo ())).

But the results still ignores everything (including bank name selections).

What do I need to change for it to allow bank name filters?

nitin_01
Creator
Creator

Hi Anat d,

 

          Using {1} in set  will disregard or ignore every selection and it will calculate for whole data set loaded in the document.

So, to disregard any field you need to use that field in set like below

                   Set Analysis  :   Function( {<'Fieldname='>} Field)

this would disregard or ignore selection of field mention in you set as said by @ Suhas Sutariya in above comment.

So expression would be like :

                                                   rangesum (above(Sum({<Date=>}income)-Sum({<Date=>}expense),0,RowNo()))

If not getting output then, need more inputs from you for desired result.

Regards,

Nitin.