Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
tcameron
Contributor II
Contributor II

Qlik Sense 30 Day calculation ignoring date filters

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.

5 Replies
kpkumar
Contributor III
Contributor III

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
Partner
Partner

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)

tcameron
Contributor II
Contributor II
Author

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.

tcameron
Contributor II
Contributor II
Author

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