Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fzalexanderjohn
New Contributor III

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!

Labels (2)
1 Solution

Accepted Solutions
rogerpegler
Contributor

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:

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!

 

 

7 Replies
Highlighted
miskinmaz
Contributor II

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

You can use the AS of table and set analysis for the same
fzalexanderjohn
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?

MVP
MVP

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

Try like:

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

Capture.PNG

fzalexanderjohn
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.

rogerpegler
Contributor

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:

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
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! 

 

Nikmartin
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.