Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fzalexanderjohn
Creator
Creator

Sum where date is greater than current date in record

Hello,

I have this table

DateChange in Stock
01.01.201910
02.01.201923
03.01.201912
04.01.2019345
05.01.2019-23
06.01.2019-400
07.01.201912
08.01.2019346
09.01.201934
10.01.2019123
11.01.2019423

 

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:

DateChange in StockTotal Since
01.01.201910905
02.01.201923895
03.01.201912872
04.01.2019345860
05.01.2019-23515
06.01.2019-400538
07.01.201912938
08.01.2019346926
09.01.201934580
10.01.2019123546
11.01.2019423423

 

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!

1 Solution

Accepted Solutions
rogerpegler
Creator II
Creator II

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:

Load Distinct Date

Resident Data;

2 Create a new result table with no rows eg

Results:

Load Total

Autgenerate (0);

3 Loop through the dates eg

for i = 0 to noofrows(DateList)-1

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

Concatenate (Results)

Load vLoopDate as Date

Sum[Change in Stock] as Total

Resident Data

Where Date >= vLoopDate;

Next

 

Comments

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!

 

 

View solution in original post

7 Replies
miskinmaz
Creator III
Creator III

You can use the AS of table and set analysis for the same
fzalexanderjohn
Creator
Creator
Author

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?

tresesco
MVP
MVP

Try like:

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

Capture.PNG

fzalexanderjohn
Creator
Creator
Author

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.

rogerpegler
Creator II
Creator II

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:

Load Distinct Date

Resident Data;

2 Create a new result table with no rows eg

Results:

Load Total

Autgenerate (0);

3 Loop through the dates eg

for i = 0 to noofrows(DateList)-1

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

Concatenate (Results)

Load vLoopDate as Date

Sum[Change in Stock] as Total

Resident Data

Where Date >= vLoopDate;

Next

 

Comments

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!

 

 

fzalexanderjohn
Creator
Creator
Author

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! 

 

Nikmartin
Contributor
Contributor

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