topic Re: Cumulative Sum in load scipt in App Development

Cumulative Sum in load scipt

ZimaBlue — Thu, 27 Jul 2023 13:00:17 GMT

Hello!,

I'm trying to find a way to calculate the cumulative total in the load script. I found an article with a formula like Range Sum(Above(sum(Sales),0,Rovno())), but as far as I understand, this does not work in the load script, because qlik sees Above as a field name, not as a function.

What I need: count for each customer|date row the sum of all customer orders for the last 30 days. The database is sorted as order by user_id, order_date asc.

I need to check that in all the previous 29 rows the user_id is the same and calculate the cumulative total in each row (data is needed for every day). So something like:

user1| order_date1 | sum of all orders for last 30 days from this date

user1| order_date2| sum of all orders for last 30 days from this date

Also, if there is less than 30, I want to see the cumulative total for the period that we have, within 30 days. Unfortunately, our sql version is very old, so I can't calculate this sum in the sql script. I need to calculate this in qlik sense.

Thank you for any help!

Re: Cumulative Sum in load scipt

cristianj23a — Thu, 27 Jul 2023 15:39:23 GMT

Hi ZimaBlue.

Here's an example script that might help you. This example assumes you have a table called 'Orders' that contains a 'User_Id', 'Order_Date', and 'Order_Amount' fields:

RawData:
LOAD
User_Id,
Order_Date,
Order_Amount
FROM
Orders
ORDER BY
User_Id,
Order_Date ASC;

TempTable:
LOAD
User_Id,
Order_Date,
Order_Amount,
IterNo() as RowNum
RESIDENT
RawData
WHILE IterNo() <= Peek('RowNum', -1, 'RawData') + 30 AND Peek('User_Id', -IterNo()) = User_Id;

FinalTable:
LOAD
User_Id,
Order_Date,
Sum(Order_Amount) as RollingOrderSum
RESIDENT
TempTable
GROUP BY
User_Id,
Order_Date;

DROP TABLES
RawData,
TempTable;

The script first loads the original data into a temporary table 'RawData' in the order of 'User_Id' and 'Order_Date'. Then, it creates a second temporary table 'TempTable' that replicates each row from 'RawData' into as many rows as there are within the previous 30 rows (or fewer if there are less than 30) with the same 'User_Id'. Finally, it creates 'FinalTable' by aggregating 'TempTable' to get the sum of 'Order_Amount' for each 'User_Id' and 'Order_Date'. The 'RawData' and 'TempTable' are then dropped as they're no longer needed.

Please note that this script assumes that you have a single record for each user and date. If you have multiple records for the same user and date, you should pre-aggregate them before using this script. Also, be aware that if your data is large, this script may take a long time to execute.

Regarts.

Re: Cumulative Sum in load scipt

ZimaBlue — Thu, 03 Aug 2023 10:07:04 GMT

Thank you for such a detailed answer! Sorry for the long answer. I get an empty table at this stage.

TempTable:
LOAD
User_Id,
Order_Date,
Order_Amount,
IterNo() as RowNum
RESIDENT
RawData
WHILE IterNo() <= Peek('RowNum', -1, 'RawData') + 30 AND Peek('User_Id', -IterNo()) = User_Id;

I also tried to remove the quotes for the field names, but it didn't help. Can I do something wrong?

Re: Cumulative Sum in load scipt

cristianj23a — Thu, 03 Aug 2023 15:08:26 GMT

Hello I don't see the quotes in your data or I don't understand your question well, but one way to remove values that you don't want is using REPLACE or SUBFIELD.

https://help.qlik.com/en-US/qlikview/May2023/Subsystems/Client/Content/QV_QlikView/Scripting/StringFunctions/Replace.htm#:~:text=Replace()%20returns%20a%20string,works%20from%20left%20to%20right.

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions/SubField.htm

Regarts.