Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
satish25
New Contributor III

Inventory Ageing For Multiple Materials

Hello QlikExperts,

     I wanted to find the "Inventory Ageing" in Buckets for Different Materials ranging from 0-30 Days, 31-60 Days, 61-90 Days, 91-120 Days, 121-150 Days, 151-180 Days, 181-270 Days, 271-365 Days, 366-730 Days, and >730 Days. The Quantities in the Ageing  should be in FIFO (First In First Out) basis.

     'Debit' is "Ordered In" and 'Credit' is "Sold Out"

     So Debit will always be Greater than or Equal to Credit.

     The Date Format is in 'MM/DD/YY'.

     I am attaching a part of the data as a screenshot below.

Capture_1.JPG

The Output shown must be as follows which is shown above.

The Material Quantities are (Debit-Credit).

1 Solution

Accepted Solutions

Re: Inventory Ageing For Multiple Materials

Tried in QlikView and it seems to be working

Table:

LOAD [Document Date],

    Material,

    [Debit/Credit Status],

    Quantity

FROM

[..\..\..\Downloads\Inventory Ageing (1).xlsx]

(ooxml, embedded labels, table is Sheet1);


Left Join (Table)

LOAD Material,

Sum(If([Debit/Credit Status] = 'Debit', Quantity)) as DebitTotalQuantity,

Sum(If([Debit/Credit Status] = 'Credit', Quantity)) as CreditTotalQuantity

Resident Table

Group By Material;


Left Join (Table)

LOAD *,

If(Material = Previous(Material), Previous(CumQuantity), 0) as PreviousCumQuantity;

LOAD [Document Date],

    Material,

    [Debit/Credit Status],

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

Resident Table

Where [Debit/Credit Status] = 'Debit'

Order By Material, [Document Date] desc;


Left Join(Table)

LOAD *,

If(Today() - [Document Date] <= 30, Dual('0-30 Days', 1),

If(Today() - [Document Date] <= 60, Dual('31-60 Days', 2),

If(Today() - [Document Date] <= 90, Dual('61-90 Days', 3),

If(Today() - [Document Date] <= 120, Dual('91-120 Days', 4),

If(Today() - [Document Date] <= 150, Dual('121-150 Days', 5),

If(Today() - [Document Date] <= 180, Dual('151-180 Days', 6),

If(Today() - [Document Date] <= 270, Dual('181-270 Days', 7),

If(Today() - [Document Date] <= 365, Dual('271-365 Days', 8),

If(Today() - [Document Date] <= 730, Dual('366-730 Days', 9), Dual('>730 Days', 10)))))))))) as Bucket,

If(DebitTotalQuantity-CreditTotalQuantity >= CumQuantity, Quantity,

If(DebitTotalQuantity-CreditTotalQuantity > PreviousCumQuantity, RangeMin(Quantity, DebitTotalQuantity-CreditTotalQuantity-PreviousCumQuantity))) as New_Quantity

Resident Table

Where [Debit/Credit Status] = 'Debit'

Order By Material, [Document Date] desc;

Pivot table

Dimensions

Material

Bucket

Expression

=Sum(New_Quantity)

20 Replies
devarasu07
Honored Contributor II

Re: Inventory Ageing For Multiple Materials

Hi,

you can try to create age - bucket like below

Buckets

Creating Aging Buckets within QlikView

satish25
New Contributor III

Re: Inventory Ageing For Multiple Materials

Thanks for your response

But here Credit has to be subtracted from Debit and the remaining Debit Quantities have to be arranged in Day Buckets

devarasu07
Honored Contributor II

Re: Inventory Ageing For Multiple Materials

Hi

yeah it should work, try like below

Fact:

load *,

  If( Aging <= 30, Dual('0-30',1),

  If( Aging >= 30 and Aging <=60 , Dual('31-60',2),

  If( Aging >= 61 and Aging<=90 , Dual('61-90',3),

  If( Aging >= 91 and Aging<=120 , Dual('91-120',4),

  If( Aging >= 121 and Aging<=150 , Dual('121-150',5),

  If( Aging >= 151 and Aging<=180 , Dual('151-180',6),

  If( Aging >= 181 and Aging<=270 , Dual('181-270',7),

  If( Aging >= 271 and Aging<=365 , Dual('271-365',8),

If( Aging >= 366 and Aging <=730 , Dual('366-730',9), Dual('>730',10)))))))))) as Bucket;

load *,

Interval(Now()- [Document Date] ,'dd') as Aging;

LOAD * INLINE [

    Document Date, Material, Debit/Credit Status, Quantity

    1/1/2015, A, Debit, 50

    3/1/2015, B, Debit, 75

    3/1/2015, C, Debit, 25

    6/1/2015, B, Credit, 20

    8/1/2015, A, Debit, 20

    12/1/2015, A, Credit, 40

    2/1/2016, A, Debit, 30

    3/1/2016, C, Debit, 10

    7/1/2016, B, Debit, 50

    11/1/2016, B, Credit, 80

    5/1/2017, A, Debit, 10

    7/1/2017, C, Credit, 30

    8/1/2017, A, Credit, 20

    12/1/2017, A, Credit, 10

];

Capture.JPG

devarasu07
Honored Contributor II

Re: Inventory Ageing For Multiple Materials

Hi,

Yes, just need to add the below measure using set analysis method

sum( {$<[Debit/Credit Status]={'Debit'}>}Quantity)-sum( {$<[Debit/Credit Status]={'Credit'}>}Quantity)


Fact:

load *,

  If( Aging <= 30, Dual('0-30',1),

  If( Aging >= 30 and Aging <=60 , Dual('31-60',2),

  If( Aging >= 61 and Aging<=90 , Dual('61-90',3),

  If( Aging >= 91 and Aging<=120 , Dual('91-120',4),

  If( Aging >= 121 and Aging<=150 , Dual('121-150',5),

  If( Aging >= 151 and Aging<=180 , Dual('151-180',6),

  If( Aging >= 181 and Aging<=270 , Dual('181-270',7),

  If( Aging >= 271 and Aging<=365 , Dual('271-365',8),

If( Aging >= 366 and Aging <=730 , Dual('366-730',9), Dual('>730',10)))))))))) as Bucket;

load *,

Interval(Now()- [Document Date] ,'dd') as Aging;

load * from table;

Tks,Deva

satish25
New Contributor III

Re: Inventory Ageing For Multiple Materials

I want the difference in the Credit and Debit quantities to be displayed in Day Buckets, In FIFO (First In First Out) Basis.

I can find the difference in the credit and debit quantities using Set Analysis

Re: Inventory Ageing For Multiple Materials

Would you be able to share the above data in an Excel file?

satish25
New Contributor III

Re: Inventory Ageing For Multiple Materials

Here You go Sunny,

The output is there in the same sheet as well

Re: Inventory Ageing For Multiple Materials

I am not 100% sure devarasu07‌, but I think you are completely ignoring the concept of FIFO here... the idea seems to be able to check inventory coming in and inventory going out and then display the time it stayed within the company based on the principal of First In First Out....

satish25
New Contributor III

Re: Inventory Ageing For Multiple Materials

Yes

The concept of FIFO has to be followed. And the bucket difference in the Debit and Credit items have to be shown