Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
nlee1993
New 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

1 Solution

Accepted Solutions

Re: Calculated field in script

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

9 Replies
nlee1993
New Contributor III

Re: Calculated field in script

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

Re: Calculated field in script

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

nlee1993
New Contributor III

Re: Calculated field in script

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?

Re: Calculated field in script

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

nlee1993
New Contributor III

Re: Calculated field in script

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

Re: Calculated field in script

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

nlee1993
New Contributor III

Re: Calculated field in script

 

Re: Calculated field in script

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
New Contributor III

Re: Calculated field in script

That is brilliant - many thanks!