Inventory Ageing For Multiple Materials

[satish25]

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.

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

The Material Quantities are (Debit-Credit).

[devarasu07]

Hi,

you can try to create age - bucket like below

Buckets

Creating Aging Buckets within QlikView

[satish25]

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]

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
];

[devarasu07]

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]

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

[sunny_talwar]

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

[satish25]

Here You go Sunny,

The output is there in the same sheet as well

[sunny_talwar]

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]

Yes

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