Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
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
Oh i see, not familiar with that. hope you can help u on that. tks
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))))
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
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))))
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' ?
I think you should be able to do this in the script.... will have to think a little about it...
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)
Thanks a lot Sunny
This works perfectly.
Cheers Mate !!!
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.