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).

[satish25]

I don't want to show the entire inventory in buckets. After differentiating the Debit inventory and Credit inventory, the remaining Debit inventory has to be shown in Buckets in FIFO method

[devarasu07]

Oh i see, not familiar with that. hope you can help u on that. tks

[sunny_talwar]

Try this may be

Dimensions

Material

=Aggr(

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 30, Dual('0-30 Days', 1),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 60, Dual('31-60 Days', 2),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 90, Dual('61-90 Days', 3),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 120, Dual('91-120 Days', 4),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 150, Dual('121-150 Days', 5),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 180, Dual('151-180 Days', 6),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 270, Dual('181-270 Days', 7),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 365, Dual('271-365 Days', 8),

If(Today() - If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), [Document Date],

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

[Document Date]))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1) <= 730, Dual('366-730 Days', 9), Dual('>730 Days', 10))))))))))

, Material, ([Document Date], (NUMERIC, DESC)))

Expression

=Sum(Aggr(If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity),

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

RangeMin(Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity), (Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)))))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1), Material, ([Document Date], (NUMERIC, DESC))))

[satish25]

Hi Sunny,

Thanks a lot man for taking time to help me with my problem.

However when I added some more Data, the Results are correct for Materials 'A' & 'C', But the Values are showing "+5" In Materials 'B' & 'D'

I am attaching the excel sheet as well the .qvf file

There can be an error from my end as well,

So it would be great if you'd let me know

Thanks once again

[sunny_talwar]

Try this

=Sum(Aggr(If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) >=

RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity)* Avg({<[Debit/Credit Status] = {'Debit'}>}1), 0, RowNo())), Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity),

If((Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)) > (Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0)* Avg({<[Debit/Credit Status] = {'Debit'}>}1)),

RangeMin(Sum({<[Debit/Credit Status] = {'Debit'}>}Quantity), (Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Debit'}>}Quantity)-Sum(TOTAL <Material>{<[Debit/Credit Status] = {'Credit'}>}Quantity)-(Alt(Above(RangeSum(Above(Sum({<[Debit/Credit Status] = {'Debit'}>} Quantity), 0, RowNo()))), 0))))))

* Avg({<[Debit/Credit Status] = {'Debit'}>}1), Material, ([Document Date], (NUMERIC, DESC))))

[satish25]

Thanks a lot Sunny, You are truly a Genius.

Cheers !!

However I have a Database That consists around 4 million records.

So is it possible to apply this code in the 'Data Load Editor' and what changes are to made if it has to be applied in the 'Data Load Editor' ?

[sunny_talwar]

I think you should be able to do this in the script.... will have to think a little about it...

[sunny_talwar · Accepted solution]

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)

[satish25]

Thanks a lot Sunny

This works perfectly.

Cheers Mate !!!

[satish25]

Sunny, I was using this logic on Excel Sheets and its was working perfectly.

However when I started using this logic on Data retrieved from a Database (SAP), the logic is not working.

Now the data is also coming from Multiple tables from the Database which we are joining to make a single table.

So is there a wayout to this problem.