Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello guys, I am trying to create invoice buckets for one of my clients
first I define my variable like this :
let vDate = Date(Today());
table:
LOAD
Customer,
InvoiceNo,
InvoiceDate,
BookEntryDate,
TransactionNo,
Currency,
"Amount LocCur",
if('$(vDate)' - Date(InvoiceDate)>='7','7 days',
if('$(vDate)' - Date(InvoiceDate)>='30','30 days',
if('$(vDate)' - Date(InvoiceDate)>='90','90 days',
if('$(vDate)' - Date(InvoiceDate)>='180','180 days',
if('$(vDate)' - Date(InvoiceDate)>='360','360 days','Not Yet'))))) as Timebuckets
from ....
But in the Pivot Chart I made with Customers, I see only Not Yet...
What am I doing wrong?
Is your InvoiceDate in the Past? It didn't work for me, until I switched the greater/equal to signs. Here are my results, and the script I used.
LET vDate = Date(Today())
;
BucketedInvoices:
LOAD *,
'$(vDate)' - InvoiceDate AS DateDifference,
If( '$(vDate)' - InvoiceDate <= 7 , '7 days',
If( '$(vDate)' - InvoiceDate <= 30 , '30 days',
If( '$(vDate)' - InvoiceDate <= 90 , '90 days',
If( '$(vDate)' - InvoiceDate <= 180 , '180 days' ,
'NO DAYS') )
)
) AS 'TimeBuckets'
;
LOAD *,
DATE#(InvoiceDateString,'YYYY-MM-DD') AS 'InvoiceDate'
;
LOAD * Inline
[
'CustomerName', 'InvoiceDateString', 'Amt'
'Dell', '2017-07-28', 100
'Dell', '2017-08-25', 200
'Dell', '2017-09-05', 300
'Dell', '2017-10-02', 400
'Dell', '2017-10-31', 500
'Dell', '2017-11-27', 600
'Dell', '2017-12-04', 700
'HP', '2017-12-15', 800
'HP', '2017-12-25', 900
'HP', '2017-12-26', 1000
'HP', '2018-01-08', 1100
'HP', '2018-01-23', 1200
'HP', '2018-01-24', 1300
'HP', '2018-02-15', 1400
'HP', '2018-02-22', 1500
]
;
DROP FIELD InvoiceDateString
;
EXIT Script
;
I am using custom extension to Update the variable..
try that
table:
LOAD
Customer,
InvoiceNo,
InvoiceDate,
BookEntryDate,
TransactionNo,
Currency,
"Amount LocCur",
if($(vDate) - Date(InvoiceDate)>=7,'7 days',
if($(vDate) - Date(InvoiceDate)>=30,'30 days',
if($(vDate) - Date(InvoiceDate)>=90,'90 days',
if($(vDate) - Date(InvoiceDate)>=180,'180 days',
if($(vDate) - Date(InvoiceDate)>=360,'360 days','Not Yet'))))) as Timebuckets
Sorry, this isn't working as well...
What is your variable vDate set to ?
Hello.
Can you try this please.
let vDate = Num(Date(Today()));
table:
LOAD
Customer,
InvoiceNo,
InvoiceDate,
BookEntryDate,
TransactionNo,
Currency,
"Amount LocCur",
if($(vDate) - Num(InvoiceDate)>=7,'7 days',
if($(vDate) - Num(InvoiceDate)>=30,'30 days',
if($(vDate) - Num(InvoiceDate)>=90,'90 days',
if($(vDate) - Num(InvoiceDate)>=180,'180 days',
if($(vDate) - Num(InvoiceDate)>=360,'360 days','Not Yet'))))) as Timebuckets
can you try
if(Todat() - InvoiceDate=>7
Regards
Have you tried the above code ? Please let us know and mark the responses.
This code is working, i am using it with a slight difference ,
if(Date(Today()) - InvoiceDate >=7, '7 days' etc.
This is working, but i Need to code the variable inside
let vDate = (Date(Today));