Calculated field in script

[nlee1993]

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

[nlee1993]

[sunny_talwar]

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

[nlee1993]

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]

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.

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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)

[nlee1993]

That is brilliant - many thanks!