Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

tcameron
New Contributor

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
lt_kpkumar
New Contributor III

Re: Qlik Sense 30 Day calculation ignoring date filters

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
Honored Contributor II

Re: Qlik Sense 30 Day calculation ignoring date filters

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

MVP
MVP

Re: Qlik Sense 30 Day calculation ignoring date filters

May be this

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

tcameron
New Contributor

Re: Qlik Sense 30 Day calculation ignoring date filters

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
New Contributor

Re: Qlik Sense 30 Day calculation ignoring date filters

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