Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
elintham
Contributor
Contributor
‎2019-03-10 11:47 PM

Cumulative Sum in Script

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:

PNMPNCPNCustomerCountryFamilyOrderIDFiscal WeekBacklogTypeSum(Qty)
CP123AB2334CDF345JohnMALAYSIAA12345201912CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201912RS1000
CP123AB2334CDF345JohnMALAYSIAA12345201913CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201913RS2000
CP123AB2334CDF345JohnMALAYSIAA12345201914CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201914RS3000
CP123AB2334CDF345JohnMALAYSIAA12345201915CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201915RS2000
CP123AB2334CDF345JohnMALAYSIAA12345201916CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201916RS2000
CP123AB2334CDF345JohnMALAYSIAA12345201917CRS1000
CP123AB2334CDF345JohnMALAYSIAA12345201917RS1000
CDS345B234DBF889KarlGERMANYB98080201912CRS5000
CDS345B234DBF889KarlGERMANYB98080201912RS5000
CDS345B234DBF889KarlGERMANYB98080201913CRS7500
CDS345B234DBF889KarlGERMANYB98080201913RS7500
CDS345B234DBF889KarlGERMANYB98080201914CRS7500
CDS345B234DBF889KarlGERMANYB98080201914RS7500
CDS345B234DBF889KarlGERMANYB98080201915CRS7500
CDS345B234DBF889KarlGERMANYB98080201915RS7500
CDS345B234DBF889KarlGERMANYB98080201918CRS5000
CDS345B234DBF889KarlGERMANYB98080201918RS5000

 

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.  

PNMPNCPNCustomerCountryFamilyOrderIDFiscal WeekBacklogTypeSum(Qty)
CP123AB2334CDF345JohnMALAYSIAA12345201912CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201912RS1000
CP123AB2334CDF345JohnMALAYSIAA12345201912Cumulative Delta-1000
CP123AB2334CDF345JohnMALAYSIAA12345201913CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201913RS2000
CP123AB2334CDF345JohnMALAYSIAA12345201913Cumulative Delta-1000
CP123AB2334CDF345JohnMALAYSIAA12345201914CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201914RS3000
CP123AB2334CDF345JohnMALAYSIAA12345201914Cumulative Delta0
CP123AB2334CDF345JohnMALAYSIAA12345201915CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201915RS2000
CP123AB2334CDF345JohnMALAYSIAA12345201915Cumulative Delta0
CP123AB2334CDF345JohnMALAYSIAA12345201916CRS2000
CP123AB2334CDF345JohnMALAYSIAA12345201916RS2000
CP123AB2334CDF345JohnMALAYSIAA12345201916Cumulative Delta0
CP123AB2334CDF345JohnMALAYSIAA12345201917CRS1000
CP123AB2334CDF345JohnMALAYSIAA12345201917RS1000
CP123AB2334CDF345JohnMALAYSIAA12345201917Cumulative Delta0
CDS345B234DBF889KarlGERMANYB98080201912CRS5000
CDS345B234DBF889KarlGERMANYB98080201912RS5000
CDS345B234DBF889KarlGERMANYB98080201912Cumulative Delta0
CDS345B234DBF889KarlGERMANYB98080201913CRS7500
CDS345B234DBF889KarlGERMANYB98080201913RS7500
CDS345B234DBF889KarlGERMANYB98080201913Cumulative Delta0
CDS345B234DBF889KarlGERMANYB98080201914CRS7500
CDS345B234DBF889KarlGERMANYB98080201914RS7500
CDS345B234DBF889KarlGERMANYB98080201914Cumulative Delta0
CDS345B234DBF889KarlGERMANYB98080201915CRS7500
CDS345B234DBF889KarlGERMANYB98080201915RS7500
CDS345B234DBF889KarlGERMANYB98080201915Cumulative Delta0
CDS345B234DBF889KarlGERMANYB98080201918CRS5000
CDS345B234DBF889KarlGERMANYB98080201918RS5000
CDS345B234DBF889KarlGERMANYB98080201918Cumulative Delta0

 

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 🙂 

Preview file
19 KB
5,912 Views
0 Likes
19 Replies
kaanerisen
Creator III
Creator III
‎2019-03-11 07:08 AM

In response to elintham

Hi,

Check the attached qvf and see how it should be 🙂

comm_help.zip
1,383 Views
0 Likes
elintham
Contributor
Contributor
‎2019-03-11 07:31 AM
Author

In response to Channa

Hi Channa, 

The script is in the second tab of the attached excel. 

Preview file
19 KB
1,378 Views
0 Likes
elintham
Contributor
Contributor
‎2019-03-11 07:32 AM
Author

In response to kaanerisen

Hi @kaanerisen, I can't open the qvf file as I'm using QlikSense Web & Desktop. I don't have QlikView. Are you able to resend in qvw format?

1,375 Views
0 Likes
kaanerisen
Creator III
Creator III
‎2019-03-11 07:44 AM

In response to elintham

If you need qvw file then you have qlikview not qlik sense. But unfortunetly I don't have qlikview.

If you copy and past the same script and change the file paths as it should be, it will work on qlikview.

1,361 Views
0 Likes
elintham
Contributor
Contributor
‎2019-03-11 07:53 AM
Author

In response to kaanerisen

Hi @kaanerisen, I have managed to open with Qlik Sense Desktop, but I'm still getting a synthetic error when I load the data. Am I supposed to ignore it? 

error.PNG

1,355 Views
0 Likes
kaanerisen
Creator III
Creator III
‎2019-03-11 08:23 AM

In response to elintham

I assume that you added the script to the app and there is a different script section on script editor. Because I see some geo tables which is created by qlik sense itself and fetch the data table twice.

Comment out the the other script part or delete the section itself and run again. It will work.

error.PNG

1,347 Views
0 Likes
elintham
Contributor
Contributor
‎2019-03-11 08:27 AM
Author

In response to kaanerisen

Hi @kaanerisen, there's an auto-generated section that i'm not able to comment out. Would this be the problem? error2.PNG

1,342 Views
0 Likes
kaanerisen
Creator III
Creator III
‎2019-03-11 08:29 AM

In response to elintham

Definetely. I guess the script that I post is in main section. If it is, unlock the auto generated section, delete the section itself and load data. It will work.

1,338 Views
0 Likes
elintham
Contributor
Contributor
‎2019-03-13 06:49 AM
Author

In response to kaanerisen

Hi @kaanerisen, got it and the script has been incorporated into my existing one. Thank you very much!

1,325 Views
0 Likes
elintham
Contributor
Contributor
‎2019-03-14 08:28 AM
Author

In response to elintham

Hi All, just wanted to ask a quick question. Basically I did three Noconcatenate tables to calculate the delta first then using if previous with rangesum and peek to do the cumulative delta calculation and then name Delta as the backlog type. But I realised that whenever I open my Qlik app, the Backlog Type = Delta will be missing from my straight table but after I reload the data, it will reappear. Then the same happens again if I close the Qlik Sense app and reopen again. Any ideas why this is happening and how to resolve it? 

1,320 Views
0 Likes