Hide Dates used to calculate running total

[hvfalcao]

Hello!

I´m evaluating Qlik Sense, so I´m new to this...

There is any way, with expressions, to use some field for calculation even if I don´t select it in a filter?

I´m using this to get the running balance of a bank account:

Total Balance: RANGESUM(ABOVE(SUM({$<ACCOUNT = P(ACCOUNT)>} BALANCE),0,ROWNO()))

This is what I´ve got (the total balance is OK so far):

But if I deselect the date 02/12/2002, the total balance got wrong:

If I use {1} in the formula it will bring me all dates, and I dont want that: RANGESUM(ABOVE(SUM({1<ACCOUNT = P(ACCOUNT)>} BALANCE),0,ROWNO()))

What I´m trying to do is to use all dates to calculate the TOTAL BALANCE, but just show the row of the selected dates on the table/graph.

[prabhuappu]

Hi Henrique,

You need to use aggr and Rangesum expression together to achieve this.

  sum(aggr(rangesum(above(total sum({<Date=>}Balance),0,3)),Date))

Refer the below link for calculating running total...

Calculating rolling n-period totals, averages or other aggregations

Hope this Helps....

Regards,

Prabhu Appu

[prabhuappu]

I have attached a qvw for better understanding... This expression will work in both Qlikview and Qliksense...

[hvfalcao · Accepted solution]

Hi Prabhu,

Thank you for your help! I could almost achieve what I want with your formula, but instead of 3, I used rowno().

"sum(aggr(rangesum(above(sum({<YEAR=, MONTH=, DATE=>}BALANCE),0,ROWNO())),DATE))"

Also, I need to SELECT SQL order by DATE asc.... Otherwise it wont work correctly.

There is only one problem now. I'm trying to use a hierarchy field (year>month>date). Do you know how to make this dynamic? If in YEAR level, the rangesum should use YEAR, if in MONTH, use MONTH, etc...

Thank you!

[prabhuappu]

Hi,

you can use getcurrentfield([group name]) function to retrieve the current field of a drill down / cyclic group.

create a variable using that function. And use that in your expression as $(vGroupname) instead of direct field name.

instead of variable you xan use $(=getcurrentfield(grpname)) also

regards,

Prabhu appu 

[hvfalcao]

Prabhu,

Thank you again! Sense requires the GetFieldSelection() instead of getcurrentfield() function:

I've created this with your suggestion:

//IF

if(isnull(GetFieldSelections([YEAR])),

//THEN

sum(aggr(rangesum(above(sum({<MONTH=, DATE=, YEAR=>}BALANCE),0,ROWNO())),YEAR)),

//ELSE

if(isnull(GetFieldSelections([MONTH])),

//THEN

sum(aggr(rangesum(above(sum({<MONTH=, DATE=, YEAR=>}BALANCE),0,ROWNO())), MONTH)),

//ELSE

sum(aggr(rangesum(above(sum({<MONTH=, DATE=, YEAR=>}BALANCE),0,ROWNO())), DATE))))