Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

Invoice Buckets

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?

1 Solution

Accepted Solutions
JustinDallas
Specialist III
Specialist III

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.

Capture33.PNG

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

;

View solution in original post

20 Replies
tomovangel
Partner - Specialist
Partner - Specialist
Author

I am using custom extension to Update the variable..

zebhashmi
Specialist
Specialist

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

tomovangel
Partner - Specialist
Partner - Specialist
Author

Sorry, this isn't  working as well...

Anonymous
Not applicable

What is your variable vDate set to ?

ychaitanya
Creator III
Creator III

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

zebhashmi
Specialist
Specialist

can you try

if(Todat() - InvoiceDate=>7

Regards

ychaitanya
Creator III
Creator III

Have you tried the above code ? Please let us know  and mark the responses.

tomovangel
Partner - Specialist
Partner - Specialist
Author

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

tomovangel
Partner - Specialist
Partner - Specialist
Author

let vDate = (Date(Today));