topic Re: Data editor: sum over last 14 values in App Development

Data editor: sum over last 14 values

QlikCommSa — Mon, 10 Mar 2025 09:51:33 GMT

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!

Re: Data editor: sum over last 14 values

Padma123 — Mon, 10 Mar 2025 11:50:00 GMT

use below expression.

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

Re: Data editor: sum over last 14 values

QlikCommSa — Mon, 10 Mar 2025 12:12:08 GMT

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.

Re: Data editor: sum over last 14 values

QlikCommSa — Wed, 12 Mar 2025 08:45:10 GMT

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? 😉

Re: Data editor: sum over last 14 values

hic — Wed, 12 Mar 2025 10:55:38 GMT

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;