topic Re: Calculate due date based on payment terms in QlikView

Calculate due date based on payment terms

ulfmattsson — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculate due date based on payment terms

vinieme12 — Thu, 26 Jan 2017 14:08:33 GMT

you need to evaluate the expressions in the front end in a chart not in the script

Re: Calculate due date based on payment terms

Anonymous — Thu, 26 Jan 2017 14:16:29 GMT

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

Re: Calculate due date based on payment terms

Anonymous — Thu, 26 Jan 2017 15:23:45 GMT

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 *;

Re: Calculate due date based on payment terms

ulfmattsson — Fri, 27 Jan 2017 07:45:39 GMT

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.

Re: Calculate due date based on payment terms

ulfmattsson — Fri, 27 Jan 2017 07:54:49 GMT

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

Re: Calculate due date based on payment terms

vinieme12 — Fri, 27 Jan 2017 11:15:18 GMT

See this post for a working sample

Loading Expressions From File - WITHOUT creating variables in Script

=$(=Only({<ExpressionFOR={'Sales'}>} FORMULA))

Re: Calculate due date based on payment terms

Anonymous — Fri, 27 Jan 2017 11:57:02 GMT

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.

Re: Calculate due date based on payment terms

ulfmattsson — Fri, 27 Jan 2017 14:06:59 GMT

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.

Re: Calculate due date based on payment terms

vinieme12 — Fri, 27 Jan 2017 14:15:07 GMT

you need to use it with Pick(Match(

Re: Calculate due date based on payment terms

ulfmattsson — Sun, 29 Jan 2017 21:03:55 GMT

Hi Imad,

I tried your solution but the fact that my formulas use document date seems to complicate matters.

I did a resident load to evalate the correct formula string from the first load but again the field is empty.

Invoices:
LOAD %InvID,
[Document Date],
PTCode,
Evaluate(PTCode) as [Due Date2]
FROM
Invoice data.xlsx
(ooxml, embedded labels, table is Sheet1);

Invoices2:
Load
%InvID as Inv,
[Document Date] as docdate,
PTCode as pt,
[Due Date2] as duedatestring,
Evaluate([Due Date2]) as [Due Date]
Resident Invoices;

Drop Table Invoices;

Re: Calculate due date based on payment terms

ulfmattsson — Fri, 03 Feb 2017 10:06:17 GMT

If anyone has the same problem as I did I finally found a solution through this old thread:

Inside field calculation?

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
;