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: 
nlee1993
Contributor III
Contributor III

Calculated field in script

Hi all,

I am trying to Qlik-ify an Excel table that has calculated field. I have a transaction date,  a quantity called "out", and I am trying to calculate something called "outbound" where I sum the "out" column from 30 days before the transaction date up to and including the transaction date.

I am looking to code this column in Qlik Sense and don't know how to do it. Any help would be greatly appreciated. I've been trying to work along the following lines

 

EXCEL_2019-01-30_11-54-34.png

 

 

 

EXCEL_2019-01-30_11-54-49.png

 

chrome_2019-01-30_11-55-08.png

Labels (5)
1 Solution

Accepted Solutions
sunny_talwar

Script

Table:
LOAD
    TransactionDate,
    "In",
    Out
FROM [lib://Lib/Qlik Sense example.xlsx]
(ooxml, embedded labels, table is Data);

AsOfTable:
LOAD TransactionDate as AsOfDate,
	 Date(TransactionDate -IterNo() + 1) as TransactionDate,
     -IterNo() + 1 as Flag
Resident Table
While IterNo() <= 31;

Table

Dimension

AsOfTable

Expression

Sum(-Out)

image.png

View solution in original post

9 Replies
nlee1993
Contributor III
Contributor III
Author

2019-01-30_12-01-47.png

sunny_talwar

I suggest you to use The As Of Table for this requirement.

nlee1993
Contributor III
Contributor III
Author

Thank you for your response. I understand the scripting and it looks good. However I am confused as to how to adapt this for a 30 days period, instead of a rolling month?

sunny_talwar

If you are able to share some sample data with the expected output, we might be able to help you better here

nlee1993
Contributor III
Contributor III
Author

Please let me know if this screenshot is sufficient. I have a table in Qlik Sense, of which below is an extract. I have outlined the row with Transaction Date 30/04/2010. The column I am trying to calculate, Outbound, is the sum of Out between the Transaction Date of 30/04/2010 and 30 days previous, which is 31/03/2010. Therefore 53,405 is the sum of the column "Out", highlighted in gold. As we move up or down the rows, the condition on Outbound is the same, where it is the sum of Out between the Transaction Date of the row and 30 days before that Transaction Date.EXCEL_2019-01-30_13-58-28.png

sunny_talwar

I won't be able to upload a screenshot to create a sample... would you be able to provide the Excel file?

nlee1993
Contributor III
Contributor III
Author

 
sunny_talwar

Script

Table:
LOAD
    TransactionDate,
    "In",
    Out
FROM [lib://Lib/Qlik Sense example.xlsx]
(ooxml, embedded labels, table is Data);

AsOfTable:
LOAD TransactionDate as AsOfDate,
	 Date(TransactionDate -IterNo() + 1) as TransactionDate,
     -IterNo() + 1 as Flag
Resident Table
While IterNo() <= 31;

Table

Dimension

AsOfTable

Expression

Sum(-Out)

image.png

nlee1993
Contributor III
Contributor III
Author

That is brilliant - many thanks!