Invoice Buckets

[tomovangel]

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?

[tomovangel]

Yes I tried it,

as you can see all the Sum's are in the 7 days, and the Dates are in Num format.

[zebhashmi]

Ok then i think you can try this

if(Date($(vDate)) - InvoiceDate >=7, '7 days' etc.

or may be this

if(Date(Date#($(vDate)))- InvoiceDate >=7, '7 days' etc.

[tomovangel]

Okay, basically this will not work, because if the variable is a Date field it needs to be enclosed in ''

but, I will look at other options when i can, I will mark your answers helpfull 😉 thanks Hashmi

[zebhashmi]

Think you! I would like to learn about that. what my understanding is " " are for the search. we can use it with = sign

something like that

if(Date(Date#("=$(vDate)"))- InvoiceDate >=7, '7 days' etc.

[tomovangel]

Yes, but you insert single quotes '

[JustinDallas · Accepted solution]

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
;

[zebhashmi]

OK Thank You!

[tomovangel]

Thank you Justin 🙂

Now i just need to be able to change the variable in the UI and everything is ready ;-)))

Thanks

[tomovangel]

see the Correct answer, how he put it in ' single quotes, ;-))

[jonathandienst]

There is nothing wrong with your syntax. The problem is the comparisons are the wrong way around. Every date delta greater than 7 will hit the 7 day bucket. I would do it like this:

Let vDate = NumToday());

table:

LOAD

    Customer,

    InvoiceNo,

    InvoiceDate,

    BookEntryDate,

    TransactionNo,

    Currency,

    "Amount LocCur",

    if($(vDate) - InvoiceDate >= '360', '360 days',

    if($(vDate) - InvoiceDate >= '180', '180 days',

    if($(vDate) - InvoiceDate >= '90', '90 days',

    if($(vDate) - InvoiceDate >= '30', '30 days',

    if($(vDate) - InvoiceDate >= '7', '7 days', 'Not Yet'))))) as Timebuckets

from ....