Cumulative Sum in Script

[elintham]

Hi Qlik Community, 

I have the below data whereby I have two Backlog Types (CRS and RS) and I would need to add another Backlog Type "Cumulative Delta" where I pre-calculate the cumulative difference between RS and CRS in the load script so that I can do a pivot in Qlik NPrinting. For example I have the below set of data:

PN	MPN	CPN	Customer	Country	Family	OrderID	Fiscal Week	BacklogType	Sum(Qty)
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201912	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201912	RS	1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201913	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201913	RS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201914	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201914	RS	3000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201915	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201915	RS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201916	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201916	RS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201917	CRS	1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201917	RS	1000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201912	CRS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201912	RS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201913	CRS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201913	RS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201914	CRS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201914	RS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201915	CRS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201915	RS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201918	CRS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201918	RS	5000

I'd like to pre-calculate Cumulative Delta in the load script. The calculation should be -1*CRS+RS and accumulate by week, ordered by PN, MPN, CPN, Customer, Country, Family and OrderID. But the sum(Qty) of CRS and RS should not be cumulative. I've attached the calculation method of the Cumulative Delta in excel, the desired pivot table output and my current script in Nprinting in the excel file. I'm thinking if I should be using the If(Peek() function with Rangesum, but not sure how exactly to apply.  

PN	MPN	CPN	Customer	Country	Family	OrderID	Fiscal Week	BacklogType	Sum(Qty)
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201912	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201912	RS	1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201912	Cumulative Delta	-1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201913	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201913	RS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201913	Cumulative Delta	-1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201914	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201914	RS	3000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201914	Cumulative Delta	0
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201915	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201915	RS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201915	Cumulative Delta	0
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201916	CRS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201916	RS	2000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201916	Cumulative Delta	0
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201917	CRS	1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201917	RS	1000
CP123	AB2334	CDF345	John	MALAYSIA	A	12345	201917	Cumulative Delta	0
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201912	CRS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201912	RS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201912	Cumulative Delta	0
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201913	CRS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201913	RS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201913	Cumulative Delta	0
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201914	CRS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201914	RS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201914	Cumulative Delta	0
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201915	CRS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201915	RS	7500
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201915	Cumulative Delta	0
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201918	CRS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201918	RS	5000
CDS345	B234	DBF889	Karl	GERMANY	B	98080	201918	Cumulative Delta	0

Dear @sunny_talwar, you can ignore the previous thread about grouping historical data, I'll close it. But do you have any ideas how to solve this one? Thanks! 

Anyone else who knows the solution, please help as well! 

Thanks in advanced 🙂 

[jonathandienst]

How about this:

Data:
LOAD
    PN,
    MPN,
    CPN,
    Customer,
    Country,
    Family,
    OrderID,
    "Fiscal Week" as FiscalWeek,
    BacklogType,
    "Sum(Qty)" as Qty
FROM [lib://Post1554649]
(html, utf8, embedded labels, table is @1);

Cumul:
LOAD OrderID,
    FiscalWeek,
    If(BacklogType = 'CRS', If(OrderID = Previous(OrderID), RangeSum(Peek(CumCRS), Qty), Qty), 0) as CumCRS,
    If(BacklogType = 'RS', If(OrderID = Previous(OrderID), RangeSum(Peek(CumRS), Qty), Qty), 0) as CumRS
Resident Data
Order By BacklogType, OrderID, FiscalWeek;

Cumul2:
LOAD OrderID,
    FiscalWeek,
    Sum(CumRS) - Sum(CumCRS) as Qty
Resident Cumul
Group By OrderID,
    FiscalWeek;

Left Join (Cumul2)
LOAD PN,   
	MPN,
    CPN,
    Customer,
    Country,
    Family,
    OrderID,
    FiscalWeek,
    'Cumulative Delta' as BacklogType
Resident Data
Where BacklogType = 'CRS';

Concatenate(Data)
LOAD * Resident Cumul2;

DROP Table Cumul, Cumul2;

Where connection Post1554649 points to https://community.qlik.com/t5/New-to-Qlik-Sense/Cumulative-Sum-in-Script/m-p/1554649 (this posting).

[elintham]

Hi, 

I added your script under my existing script and dropped the final table where the ApplyMap is as well but am getting an "invalid expression" error in the data table portion. I have to retain the Mapping Load and ApplyMap portion in my script as the two Backlog Types were differentiated using the Mapping Load and ApplyMap script. Any ideas on why there is an error?

The below is my current script: 

Backlog_All:

Load

*,

FROM [Lib:xxx] (qvd);

CRS:
Mapping Load
OrderID,
CRS_WEEK
Resident Backlog_All;

RS:
Mapping Load
OrderID,
RS_WEEK
Resident Backlog_All;

Final_Backlog_All:
Load
*,
ApplyMap('CRS',OrderID, 'OTHER') as FiscalWeek,
'CRS' as BacklogType,
1 as Status
Resident Backlog_All;

Load
*,
ApplyMap('RS',OrderID, 'OTHER') as FiscalWeek,
'RS' as BacklogType,
0 as Status
Resident Backlog_All;

Backlog_Table:
Load
PN,
MPN,
CPN,
Customer,
Country,
FAMILY,
OrderID,
FiscalWeek,
BacklogType,
Sum(QTY) as QTY
Resident Final_Backlog_All;

Cum1:
Load
OrderID,
FiscalWeek,
If(BacklogType = 'CRS', If(OrderID = Previous(OrderID), RangeSum(Peek(CumCRS), Qty), Qty), 0) as CumCRS,
If(BacklogType = 'RS', If(OrderID = Previous(OrderID), RangeSum(Peek(CumRS), Qty), Qty), 0) as CumRS
Resident Backlog_Table
Order By BacklogType, OrderID, FiscalWeek;

Cum2:
LOAD
OrderID,
FiscalWeek,
Sum(CumRS) - Sum(CumCRS) as Qty
Resident Cum1
Group By OrderID,
FiscalWeek;

Left Join (Cum2)
LOAD
PN,
CPN,
MPN,
Customer,
Country,
FAMILY,
OrderID,
FiscalWeek,
'Cumulative Delta' as BacklogType
Resident Backlog_Table
Where BacklogType = 'CRS';

Concatenate(Backlog_Table)
LOAD * Resident Cum2;

Drop Tables Backlog_All, Final_Backlog_All, Cum1, Cum2;

[Channa]

try this  attach QVF

Main:
LOAD
PN,
MPN,
CPN,
Customer,
Country,
Family,
OrderID,
"Fiscal Week" as FiscalWeek,
BacklogType,
"Sum(Qty)" as Qty
FROM [lib://TEST/SampleData.xlsx]
(ooxml, embedded labels, table is Sheet1);

Concatenate(Main)
COLUMNS:
LOAD
PN,
MPN,
CPN,
Customer,
Country,
Family,
OrderID,
FiscalWeek,
'TCumulate' as BacklogType,
sum(if(BacklogType='CRS', Qty)) -
sum(if(BacklogType='RS', Qty)) as Qty
Resident Main
Group by PN,
MPN,
CPN,
Customer,
Country,
Family,
OrderID,
FiscalWeek,'TCumulate'
Order By PN,
MPN,
CPN,
Customer,
Country,
Family,
OrderID,
FiscalWeek;

[kaanerisen · Accepted solution]

Hi elintham,

How about this,

Main:
LOAD
    PN,
    MPN,
    CPN,
    Customer,
    Country,
    Family,
    OrderID,
    "Fiscal Week",
    BacklogType,
    "Sum(Qty)" AS QTY
FROM [lib://comm_help]
(html, utf8, embedded labels, table is @1);

Concatenate

Calculated_Delta:
LOAD
    PN,
    MPN,
    CPN,
    Customer,
    Country,
    Family,
    OrderID,
    "Fiscal Week",
    BacklogType,
    IF(Previous(OrderID)=OrderID,RangeSum(Peek(QTY),QTY),QTY) AS QTY;
LOAD
    PN,
    MPN,
    CPN,
    Customer,
    Country,
    Family,
    OrderID,
    "Fiscal Week",
    'Cumulative Delta' AS BacklogType,
    sum(IF(BacklogType='RS',QTY,-QTY)) AS QTY
Resident Main
Group by     
    PN,
    MPN,
    CPN,
    Customer,
    Country,
    Family,
    OrderID,
    "Fiscal Week";

Hope it helps..

[elintham]

Hi Channa, 

Thanks for your attachment but I'm unable to open it. I'm using QlikSense web and desktop. 

[elintham]

Hi Kaanerisen, 

Thank you for your reply. The screenshot of the output looks like what I need but I'm getting a synthetic key when I load the data following your script. Should I be dropping the main table? I tried that but I'm not getting the Cumulative Delta as an added Backlog Type if I do so. 

[kaanerisen]

No you shouldn't drop the main table. main table and calculated table should be concatenated. can you share your script and I can look into.

[elintham]

Hi @jonathandienst, 

I re-tried this with the same sample date in Qlik Sense Desktop but i'm getting this output which isn't what I was looking for. Not sure if I've done something wrong or if this is actually the output that should be generated using your script? 

[Channa]

past your script hear