Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
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

Labels (5)

• ### help

1 Solution

Accepted Solutions
MVP

Script

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

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

Table

Dimension

`AsOfTable`

Expression

`Sum(-Out)`

9 Replies
Contributor III
Author

MVP

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

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?

MVP

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

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.

MVP

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

Contributor III
Author

MVP

Script

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

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

Table

Dimension

`AsOfTable`

Expression

`Sum(-Out)`

Contributor III
Author

That is brilliant - many thanks!

Tags
Community Browser