Qlik Sense 30 Day calculation ignoring date filters

[Anonymous]

Hello,

I am currently using the following formula:

RangeSum(Below(Sum(Aggr(FLIGHT,FLIGHT_KEY)),0,30))

In a table that I have in the visualisation I can show the date in the first column, the number of FLIGHTs for that date in the second column, and in the third column, the above formula shows the sum of the last 30 days of FLIGHTs. My problem is, if I apply a filter for a month, the first 30 days of the month don't include the last 30 days as the previous month is filtered out. How can I force the formula to include the 30 days before the first date of the filtered range?

I assume I need a {1} or a Total somewhere in there, but I have not had much success.

Thank you.

[Anonymous]

You may try

1.use above() [for previous 30 days values ]instead of below() if you need more clarity look at this post

Missing Manual - Above() and Below()

2.and also try aggr() on top of sum()  [ RangeSum(Below total (Aggr(Sum())) ]

RangeSum( Below (total Aggr(Sum(FLIGHT),FLIGHT_KEY)),0,30)

[agigliotti]

would do you have a simple app with mock data to look at ?

[sunny_talwar]

May be this

RangeSum(Below(Sum({<Month>}Aggr(Only({<Month>}FLIGHT), FLIGHT_KEY)), 0, 30)) * Avg(1)

[Anonymous]

1. The data was structured such that below was the best case. as the table was sorted that way. This is because I was doing it in the visualisation layer.

2. This results in the value 30 being placed for every record.

[Anonymous]

I changed the <Month> fields to the <FACT_DATE> fields i was using for individual dates and it seems to work, thank you.