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: 
satish25
Contributor III
Contributor III

Ageing Buckets !!

Hello Qlik Experts,

    I have the following table (Sample Data) :

CodeAccount NumberQuantityStockDateTypeValue
A01001266000550005/15/201810011000
A01001263000550004/21/201810011000
A01001261500550002/03/201710011000
A11002437000820003/03/20181004100
A11002433000820003/01/20181004100
A11002451000820012/23/20141004100
A11002435000820002/23/20141004100
A11002434000820002/02/20141004100
A550045590001500011/13/20171005000
A550045517000150001/13/20171005000
B10016663000150005/23/20181009000
B10016662000150003/03/20181009000
B10017001500150001/23/20171009000
B10017007000150001/03/20171009000
C4100300100035002/14/20161003500
C41003004035001/05/20161003500
C410030010035012/22/20151003500
E03000266000500003/01/20181007500
F220562210075012/17/20171001500
F220562580075012/17/20161001500
H2000666250001000005/15/2018100250
H200066630001000004/15/2018100250
H2000666170001000003/15/2018100250
H2000666550001000002/15/2018100250
H200066623001000001/15/2018100250
H20006661500100001/13/2018100250
K1512268300250005/07/20181002500
K15122681300250004/22/20181002500
K1512268500250003/25/20181002500
K15122681000250012/7/20171002500
L0114760500200005/04/20181002200
L0114760250200005/02/20181002200
L0114760450200004/04/20181002200
L0114760300200002/14/20181002200
L01147601000200002/10/20181002200

These are values of multiple Tables combined into a single table (Stock Value is Multiplied on account of Joining of 2 Tables).

The 'Value Field' and 'Stock Field' are related to each other

I have to calculate Ageing as per the following (Today = 05/31/2018, 'MM/DD/YY') :

  • First I have to compare the Stock and Quantity with respect to Date [From Max(Date) to Min(Date)].
    • If for a Particular Code starting from its 1st value, Quantity is greater than Stock, the Stock should appear in a Day Bucket that is equal to [Today() - Date] (Check Code A01, B10, C41, E03, H20).
    • If for a Particular Code starting from its 1st value, Stock is greater than Quantity, there will be more than 1 Bucket. Move on to the Next Date and Quantity of the same Code check the sum of the 2 Quantities now. Now if the Sum of the 2 Quantities are greater than the Stock, then the Values will be split in 2 Buckets are per the Date Difference.
      • First Bucket will have Quantity (First quantity value).
      • Second Bucket will Have Stock - (1st Quantity) [Check Code A11, A55, F22].
    • Suppose even after 2 Quantity Values, the Stock is still greater (Check Code K15), then move on to the 3rd value of Quantity and then compare with the stock value. Now there will be more then 2 Buckets:
      • 1st Bucket will have the 1st Quantity,
      • 2nd Bucket will have the 2nd Quantity.
      • 3rd Bucket will have the 3rd Quantity.
      • 4th Bucket will Have Stock - (1st Quantity + 2nd Quantity + 3rd Quantity)
    • If still the Quantity is less than stock, the same trend continues (Check Code L01).

The Date Field has to be sorted in Descending order w.r.t. Code

So My Output should be the following:

CodeAccount NumberStockTypeValue0-30 Days Stock31-60 Days Stock61-90 Days Stock91-120 Days Stock121-150 Days Stock151-180 Days Stock181-270 Days Stock271-365 Days Stock366-730 Days Stock730+ Days Stock0-30 Days Value31-60 Days value61-90 Days Value91-120 Days Value121-150 Days Value151-180 Days Value181-270 Days Value271-365 Days Value366-730 Days Value730+ Days Value
A0100126550010011000550000000000011000000000000
A1100243820010041000700001200000000035000600000000
A550045515000100500000000090000600000000003000020000
B10016661500100900015000000000009000000000000
C410030035010035000000000003500000000003500
E03000265000100750000005000000000000750000000
F2205622750100150000000100006500000002000013000
H20006661000010025010000000000000250000000000
K15122682500100250030013005000040000003001300500004000000
L01147602000100220075045008000000008254950880000000

I am attaching this on an Excel Sheet as well (Input and Output) for further Reference.


Thanks In Advance.

1 Solution

Accepted Solutions
sunny_talwar

Attaching the updated app

View solution in original post

15 Replies
sunny_talwar

Something like this?

Capture.PNG

satish25
Contributor III
Contributor III
Author

Hi Sunny,

Thanks for your reply and taking out the time to help.

However I want the Buckets (0-30, 31-60 and so on ... ) As Dimensions.

And the Stock Field & Value Field are related to each other.

So when the Stock splits in Buckets, the Value Field should also split accordingly in Buckets.

Just another request, if you can provide a .qvf file format since i use Qlik Sense then it would be of great help.

Thanks once again.

sunny_talwar

Looks better?

Capture.PNG

satish25
Contributor III
Contributor III
Author

Thanks again.

So if I Use a Cross Table will that work to get the Values In Buckets Field in Dimensions.

And if I change the Buckets statement to Suit my Bucket needs will that work as well ??

sunny_talwar

Here is for the exact same format

Capture.PNG

You can change the bucket accordingly by changing this

Replace(Class(Today() - Date, 30), ' <= x < ', '-') as Buckets

satish25
Contributor III
Contributor III
Author

This is exactly what i wanted

However is it possible to show this in a straight Table

sunny_talwar

Sure, you will just need to create 15-20 expression... one each for each of your bucket

satish25
Contributor III
Contributor III
Author

Table:

LOAD

    Code,

    "Account Number",

    Quantity,

    Stock,

    "Date",

    "Type",

    Value

FROM [lib://Hello/Sheet For Buckets.xlsx]

(ooxml, embedded labels, table is Input);

FinalTable:

LOAD *,

If(Code = Previous(Code),

If(Peek('Temp1') < 0, If(Temp1 > 0, Stock - Peek('CumQuantity'), Quantity), 0), If(Temp1 > 0, Stock, Quantity)) as Temp2;

LOAD *,

If(Code = Previous(Code), RangeSum(Peek('CumQuantity'), Quantity), Quantity) as CumQuantity,

If(Code = Previous(Code), RangeSum(Peek('CumQuantity'), Quantity), Quantity) - Stock as Temp1,

// Replace(Class(Today() - Date, 30), ' <= x < ', '-') as Buckets

          if(Today() - "Date" <= 30, Stock,'0') as [0-30 Days],

         if(Today() - "Date" > 30 and Today() - "Date" <= 60, Stock,'0') as [31-60 Days],

         if(Today() - "Date" > 60 and Today() - "Date" <= 90, Stock,'0') as [61-90 Days],

         if(Today() - "Date" > 90 and Today() - "Date" <= 120, Stock,'0') as [91-120 Days],

         if(Today() - "Date" > 120 and Today() - "Date" <= 180,  Stock,'0') as [121-180 Days],

         if(Today() - "Date" > 180 and Today() - "Date" <= 270, Stock,'0') as [181-270 Days],

         if(Today() - "Date" > 270 and Today() - "Date" <= 365,  Stock,'0') as [271-365 Days],

         if(Today() - "Date" > 365, Stock,'0') as [>365 Days]

Resident Table

Order By Code, Date desc;

DROP Table Table;

Dim:

LOAD * INLINE [

Dim

1

2

];

I tried this code, However it was not splitting the stock as per Buckets.

sunny_talwar

Attach back the qvf please