
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate due date based on payment terms
Hi,
I'm trying to calculate due date for invoices based on payment terms. We have 68 different payment terms with different formulas for each. As suggested in an older thread
https://community.qlik.com/message/873768#873768
I have managed to store each formula as a variable with the same name as the payment term in my data file. However I can't match the variable with my data field. Could someone please help with the syntax or other suggestion?
Invoices:
LOAD
%InvID,
[Document Date],
PTCode,
Date([Document Date]+1) as duda, //Example formula
$(PT01) as [Due Date 1], //Fixed variable works
Evaluate('$(' & PTCode & ')') as [Due Date 2] //Getting correct variable from PTCode field does not work
FROM
Data source
Thanks
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
If anyone has the same problem as I did I finally found a solution through this old thread:
Variables:
Load * Inline [
PTCode, Formula
PT01, Date('DocDate'+1)
PT02, Date('DocDate'+2)
PT03, Date('DocDate'+3)
PT04, Date('DocDate'+4)
PT05, Date('DocDate'+5)
];
For vRowNo = 0 to NoOfRows('Variables')-1
Let vVariableName = Peek('PTCode',vRowNo,'Variables');
Let $(vVariableName) = Peek('Formula',vRowNo,'Variables');
Next vRowNo
Drop table Variables;
Data:
LOAD %InvID as ID,
[Document Date] as DocDate,
Evaluate(PTCode) as Calc,
PTCode
FROM
Invoice data.xlsx
(ooxml, embedded labels, table is Sheet1);
LET vCalcs = repeat('replace(',nooffields('Data')) & 'Calc';
FOR I = 1 TO nooffields('Data')
LET vCalcs = vCalcs & ',' & chr(39) & fieldname(I,'Data') & chr(39) & ',' & fieldname(I,'Data') & ')';
NEXT
LEFT JOIN (Data)
LOAD
ID
,$(vCalcs) as VarForm
,evaluate($(vCalcs)) as Result
RESIDENT Data
;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you need to evaluate the expressions in the front end in a chart not in the script
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I did not think you need the single quote to construct Due date 2
Evaluate('$(' & PTCode & ')') as [Due Date 2]
Evaluate($(PTCode)) as [Due Date 2]
it will be helpful to see a sample data

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please try this:
Evaluate(PTCode) as [Due Date 2] instead. PTCode will replaced by the variable it represents.
Try this
let vA = NUM(Today()+1);
T1:
LOAD * INLINE [
Dim, Sales
vA, 150
vA, 200
B, 240
B, 230
];
QUALIFY *;
T2:
LOAD Dim,
EVALUATE(Dim)
RESIDENT T1;
UNQUALIFY *;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for the suggestion but Evaluate(PTCode) only gives me the formula as a string. It doesn't calculate based on [Document Date].
This is sample variables loaded from Excel
PTCode, Formula
PT01, Date([Document Date]+1)
PT02, Date([Document Date]+2)
PT03, Date([Document Date]+3)
PT04, Date([Document Date]+4)
PT05, Date([Document Date]+5)
I tried to put an equal sign in front of the formula but still only a string is returned.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Vineeth
Could you please give an example expression how this can be done? How do I match the right variable in relation to payment term?
Thanks


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
See this post for a working sample
Loading Expressions From File - WITHOUT creating variables in Script
=$(=Only({<ExpressionFOR={'Sales'}>} FORMULA))
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Please have a look at the attached. I have created inlines tables, one based on formula and one on variable. It seems to work ok , I actually see the values as a result of the formula or a variable.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you Vineeth for the example. However I didn't manage to apply it to my case. I want all formulas to be used in one field "due date" not in a separate field for each formula. Besides I want to create further calculations based on due date which is why I want to calculate it in load.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you need to use it with Pick(Match(
If a post helps to resolve your issue, please accept it as a Solution.

- « Previous Replies
-
- 1
- 2
- Next Replies »