Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

krithikarees
New Contributor

Aging Buckets

 

1.For 0 DAYS the condition is “Net Due date >= System date & Posting date <= System date”

3/15/2018 > 2/5/2018 &   12/15/2017 < 2/5/2018 - Condition TRUE

  So, first records satisfy the above condition and amount is under 0 days bucket.

 

2.For 0 DAYS the condition “Net Due date between (System date - 1 to - 30) & Posting date <= System date”

1/21/2018 is between 1/16/2018 - 2/4/2018 & 10/23/2017 < 2/5/2018 à Condition TRUE

  So, second records satisfy the above condition and amount is under 1-30 days bucket.

How do I create these buckets in qliksense?

 

 

1 Solution

Accepted Solutions
JustinDallas
Valued Contributor

Re: Aging Buckets

You could try something like this.  Notice how I perform my bucketing calculation when creating the Buckets table.

Data:

LOAD InvoiceId,

Date(Date1,'M/D/YYYY') AS 'Net Due Date',

Date(Date2,'M/D/YYYY') AS 'System Date',

Date(Date2,'M/D/YYYY') AS 'Posting Date',

    Interval(Date(Date1,'M/D/YYYY') - Date(Date2,'M/D/YYYY'),'dd') AS 'Intervals'

;

LOAD * Inline

[

'InvoiceId', 'Date1', 'Date2', 'Date'

    1,'2/27/2017','1/4/2018','9/14/2018',

    2,'2/23/2018','9/1/2016','2/22/2016',

    3,'10/30/2017','11/24/2017','7/31/2017',

    8'6/7/2016','2/15/2018','11/2/2018'

    9,'3/15/2018','3/5/2018','12/15/2017'

    10,'1/21/2018','1/16/2018','10/23/2017'  

]

;

Buckets:

Load InvoiceId, '0 days' AS 'Invoice Aging'

Resident Data

    Where [Net Due Date] >= [System Date]

    AND [Posting Date] <= [System Date]

;

Load InvoiceId, '1-30 days' AS 'Invoice Aging'

Resident Data

    Where Interval([System Date] - [Net Due Date],'dd') > 1

    AND Interval([System Date] - [Net Due Date],'dd') < 30

    AND [Posting Date] <= [System Date]

;

Let me know if this helps.

3 Replies
JustinDallas
Valued Contributor

Re: Aging Buckets

You could try something like this.  Notice how I perform my bucketing calculation when creating the Buckets table.

Data:

LOAD InvoiceId,

Date(Date1,'M/D/YYYY') AS 'Net Due Date',

Date(Date2,'M/D/YYYY') AS 'System Date',

Date(Date2,'M/D/YYYY') AS 'Posting Date',

    Interval(Date(Date1,'M/D/YYYY') - Date(Date2,'M/D/YYYY'),'dd') AS 'Intervals'

;

LOAD * Inline

[

'InvoiceId', 'Date1', 'Date2', 'Date'

    1,'2/27/2017','1/4/2018','9/14/2018',

    2,'2/23/2018','9/1/2016','2/22/2016',

    3,'10/30/2017','11/24/2017','7/31/2017',

    8'6/7/2016','2/15/2018','11/2/2018'

    9,'3/15/2018','3/5/2018','12/15/2017'

    10,'1/21/2018','1/16/2018','10/23/2017'  

]

;

Buckets:

Load InvoiceId, '0 days' AS 'Invoice Aging'

Resident Data

    Where [Net Due Date] >= [System Date]

    AND [Posting Date] <= [System Date]

;

Load InvoiceId, '1-30 days' AS 'Invoice Aging'

Resident Data

    Where Interval([System Date] - [Net Due Date],'dd') > 1

    AND Interval([System Date] - [Net Due Date],'dd') < 30

    AND [Posting Date] <= [System Date]

;

Let me know if this helps.

Highlighted
krithikarees
New Contributor

Re: Aging Buckets

Thank you Justin . I should have been clear with my question.This is my Load statement - where System Date is today.

Data:

[Company code_COMP_CODE] as [Company Code],
[Profit Center_PROFIT_CTR] as [Profit Center],
Country_COUNTRY as [Country],
[Sales document_DOC_NUMBER] as [Sales Document],
[Created on_CREATEDON] as [Created On],
[Local currency_LOC_CURRCY] as [Local Currency],
[Fiscal year/period_FISCPER] as [Fiscal Year],
[Document Date_DOC_DATE] as [Documented Date],
[Posting date_PSTNG_DATE] as [Posting Date],
[Business area_BUS_AREA] as [Business Area],
Currency_CURRENCY as [Currency],
// Customer_DEBITOR as [Customer Number],
(replace(ltrim(Replace(Customer_DEBITOR, '0', ' ')),' ','0')) as [Customer Number],
[G/L Account_GL_ACCOUNT] as [G/L Account],
Doc.number_AC_DOC_NO as [Document Number],
[Account type_ACCT_TYPE] as [Account Type],
[Document type_AC_DOC_TYP] as [Document Type],
[Posting key_POST_KEY] as [Posting Key],
Clearing_CLEAR_DATE as [Clearing Date],
[Net due date_NETDUEDATE] as [Net Due Date],
[Debit amt in LC_DEBIT_LC],
[Credit amt in LC_CREDIT_LC],
[Debit/Credit Amount_DEB_CRE_LC] as [Balance Amount],
Reference_REF_DOC_NO,
Invoice_INV_DOC_NO,
[Invoice Item_INV_ITEM],
[Invoice Year_INV_YEAR],
[Invoice Date_/BIC/ZZINVDATE]as [Invoice Date]
FROM
[\Shared Stage 1 Target QVDs\VIC\QUAL\Accounts Receivable.QVD]
(qvd);

I am trying to create my buckets in the script.

1.For 0 DAYS the condition is “Net Due date >= System date & Posting date <= System date”

3/15/2018 > 2/5/2018 &   12/15/2017 < 2/5/2018 - Condition TRUE

  So, first records satisfy the above condition and amount is under 0 days bucket.

 

2.For 0 DAYS the condition “Net Due date between (System date - 1 to - 30) & Posting date <= System date”

1/21/2018 is between 1/16/2018 - 2/4/2018 & 10/23/2017 < 2/5/2018 à Condition TRUE

  So, second records satisfy the above condition and amount is under 1-30 days bucket.

and so on to 30 - 60 , 60 - 90 , >120

JustinDallas
Valued Contributor

Re: Aging Buckets

[System Date] may be today, in that case you would set today to a variable

$vToday = Today()

;

Then you would have something like:

Load InvoiceId, '1-30 days' AS 'Invoice Aging' 

Resident Data 

      Where Interval('$(vToday)' - [Net Due Date],'dd') > 1 

        AND Interval('$(vToday)' - [Net Due Date],'dd') < 30 

        AND [Posting Date] <= '$(vToday)' 

Community Browser