Data editor: sum over last 14 values

[QlikCommSa]

Hi,

I'm using Qlik Sense November 2024. 

I load data from excel sheets, that look the following:

Date, Location, NumberOfVisitors

01.01.2025, Berlin, 3000

01.01.2025, Munich, 4000

02.01.2024, Berlin, 1900

...

After loading I want to add a column where the sum of visitors of the last 14 days per location is shown:

Date, Location, NumberOfVisitors, NumberOfVisitorsLast14Days

01.01.2025, Berlin, 3000, [number of visitors from 18.-31.12.2024] 40000

01.01.2025, Munich, 4000, [number of visitors from 18.-31.12.2024] 48000

02.01.2024, Berlin, 1900, [number of visitors from 19.12.2024-01.01.2025] 39500

...

What formula do I have to use?

Thanks in advance!

[Padma123]

use below expression.

rangesum(above(aggr(Sum(NumberOfVisitors),Date),0,14))

[QlikCommSa]

Hi Padma,

thanks for your quick reply! 

I can't use this expression in data editor. I need to have a solution for my Qlik Sense script. 

[QlikCommSa]

Hi,

since I had to find a solution now, I did the following:

VisitorMap:
Mapping

Load Date & '-' Location as Key, NumberOfVisitors

from visitors (qvd);

visitors:

noconcatenate

load Date, Location, NumberOfVisitors,

ApplyMap('VisitorMap',Date-1 & '-' Location) + ApplyMap('VisitorMap',Date-2 & '-' Location) + ...

from visitors (qvd);

Any comments or critics for this solution? 😉

[hic · Accepted solution]

Your solution will probably work fine, if you format the date as an integer number: Num(Date). A different way to do it could be:

tmpVisitors:

Load Date, Location, NumberOfVisitors 

From visitors (qvd);

Visitors:

Load Date, Location, NumberOfVisitors,

NumberOfVisitors +

If(Location=Peek(Location,-1),Peek(NumberOfVisitors,-1),0) +

If(Location=Peek(Location,-2),Peek(NumberOfVisitors,-2),0) +

...

If(Location=Peek(Location,-13),Peek(NumberOfVisitors,-13),0) as NumberOfVisitorsLast14Days

Resident tmpVisitors

Order By Location, Date;

Drop Table tmpVisitors;