Solved: Re: Invoice Buckets - Qlik Community

tomovangel · ‎2018-02-23

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?

JustinDallas · ‎2018-02-26

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
;

View solution in original post

tomovangel · ‎2018-02-23

I am using custom extension to Update the variable..

zebhashmi · ‎2018-02-23

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 · ‎2018-02-23

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

Anonymous · ‎2018-02-23

What is your variable vDate set to ?

ychaitanya · ‎2018-02-23

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 · ‎2018-02-23

can you try

if(Todat() - InvoiceDate=>7

Regards

ychaitanya · ‎2018-02-23

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

tomovangel · ‎2018-02-26

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 · ‎2018-02-26

let vDate = (Date(Today));