# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
New Contributor III

## Sum where date is greater than current date in record

Hello,

I have this table

 Date Change in Stock 01.01.2019 10 02.01.2019 23 03.01.2019 12 04.01.2019 345 05.01.2019 -23 06.01.2019 -400 07.01.2019 12 08.01.2019 346 09.01.2019 34 10.01.2019 123 11.01.2019 423

I would like a sum for all record where the date is greater than the date in the column "date". So it would look like this:

 Date Change in Stock Total Since 01.01.2019 10 905 02.01.2019 23 895 03.01.2019 12 872 04.01.2019 345 860 05.01.2019 -23 515 06.01.2019 -400 538 07.01.2019 12 938 08.01.2019 346 926 09.01.2019 34 580 10.01.2019 123 546 11.01.2019 423 423

So for the first row, sum of all change in stock where the date is greater then 01.01.2019. And for the second row sum of all change in stock where the date is greater then 02.01.2019.

Anybody ideas? Thanks!

Labels (2)

• ### sum

1 Solution

Accepted Solutions
Highlighted
Contributor II

## Re: Sum where date is greater than current date in record

It can be done in the load script by looping through each date, summing the data and concatenating the sum to a new table.

In more detail:

1 Create a new table of distinct dates in the data (or use a calendar script to generate all dates between min and max dates) eg

DateList:

Resident Data;

2 Create a new result table with no rows eg

Results:

Autgenerate (0);

3 Loop through the dates eg

for i = 0 to noofrows(DateList)-1

let vLoopDate = peek('Date', \$(i), 'DateList')

Concatenate (Results)

Sum[Change in Stock] as Total

Resident Data

Where Date >= vLoopDate;

Next

The date comparison in the Where clause may need tweaking, depending on your date format.

If you need to do the same per product code for instance then add a group by clause to the concatenate statement. Not that this will make an inefficient loop even more inefficient!

7 Replies
Highlighted
Contributor III

## Re: Sum where date is greater than current date in record

You can use the AS of table and set analysis for the same
Highlighted
New Contributor III

## Re: Sum where date is greater than current date in record

I currently read about it. So roughtly I create a second table in the load script that links the date with all dates that are greater?

Highlighted
MVP

## Re: Sum where date is greater than current date in record

Try like:

RangeSum(Below(Total [Change in Stock],0,NoOfRows()-RowNo()+1))

Highlighted
New Contributor III

## Re: Sum where date is greater than current date in record

Thank you, it works.

Sadly the performance is not great. The table in question has 3 million rows. So maybe I need to find a way  to do it in the load script.

Highlighted
Contributor II

## Re: Sum where date is greater than current date in record

It can be done in the load script by looping through each date, summing the data and concatenating the sum to a new table.

In more detail:

1 Create a new table of distinct dates in the data (or use a calendar script to generate all dates between min and max dates) eg

DateList:

Resident Data;

2 Create a new result table with no rows eg

Results:

Autgenerate (0);

3 Loop through the dates eg

for i = 0 to noofrows(DateList)-1

let vLoopDate = peek('Date', \$(i), 'DateList')

Concatenate (Results)

Sum[Change in Stock] as Total

Resident Data

Where Date >= vLoopDate;

Next

The date comparison in the Where clause may need tweaking, depending on your date format.

If you need to do the same per product code for instance then add a group by clause to the concatenate statement. Not that this will make an inefficient loop even more inefficient!

Highlighted
New Contributor III

## Re: Sum where date is greater than current date in record

Hey there,

just wanted to give you a thank you. Your approach ended up working. I had to tweak the syntax here and there but it works!

Highlighted
New Contributor

## Re: Sum where date is greater than current date in record

Thanks for sharing it. This query helps me with my project. Thank you so much for sharing it with us.