Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all, I have a table with these data:
I need to calculate the field ‘stock’, which would be the result:
(Sum (Quantity)) in Credit – (sum (Quantity)) Debit
This new field should be as a new metric, to be able to apply to graphs and tables. (imagine a chart with a horizontal time axis, showing the evolution of the stock)
Thank you for your response
Best Regards
Add these to the load (the first two lines are optional:
Sum(If([Debit/Credit] = 'Credit', Quantity) as CreditQuantity, Sum(If([Debit/Credit] = 'Debit', Quantity) as DebitQuantity, Sum(If([Debit/Credit] = 'Credit', 1, -1) * Quantity) as Stock,
can you share sample data or APP
Try this?
Table:
Load Material, Date, Quantity, [Credit/Debit] as Field From Table;
Left Join(Table)
Load Material, Date, Quantity, Field, Sum (Quantity) as [Credit Quantity] Resident Table where Field='Credit' Group By Material, Date, Quantity, Field;
Left Join(Table)
Load Material, Date, Quantity, Field, Sum (Quantity) as [Debit Quantity] Resident Table where Field='Debit' Group By Material, Date, Quantity, Field;
Final_Table:
Noconcatenate
Load Material, Date, Quantity, Field, ([Credit Quantity] - [Debit Quantity]) as Quantity_Required Resident Table;
Drop Table Table;
Add these to the load (the first two lines are optional:
Sum(If([Debit/Credit] = 'Credit', Quantity) as CreditQuantity, Sum(If([Debit/Credit] = 'Debit', Quantity) as DebitQuantity, Sum(If([Debit/Credit] = 'Credit', 1, -1) * Quantity) as Stock,
It works perfect!
But the idea is that the stock has to be always calculated from the origin (in my case since December 2016).
I mean, if I do not apply a date filter, it calculates the stock ok.
But if you select, for example, the month of March 2019, the stock must be calculated from the time source (Dec2016) until March 2019.
How can I do this? Currently if I apply the March2019 filter, the stock is calculated only considering the March2019 quantities, and it is not real because it does not take into account the movements of before.
Thank you!
Attached image
It explains better what I want to say.
Thank you!
You will need the closing stock level for minimum date of your selection. You could calculate that in the script by accumulating the net stock movements, and then add that opening stock to the movements in the selected date range. Something like:
LOAD *, If(Material = Previous(Material), Peek(OnHand), 0) + StockMove as OnHand ORDER By Material, Date ; LOAD Material, Date, Sum(If([Debit/Credit] = 'Credit', Quantity) as CreditQuantity, Sum(If([Debit/Credit] = 'Debit', Quantity) as DebitQuantity, Sum(If([Debit/Credit] = 'Credit', 1, -1) * Quantity) as StockMove FROM ... Group By Material, Date
Thanks for your kind response.
But I can not get it to work.
Before I had to create the stock measure as 'Element master' and not in the script, because it gave me 'invalid expression' I have tried again to create what you say, but the same thing happens. I attach the current script.
Could you take a look at it?
LOAD
WERKS as Centro,
LGORT as Almacen,
REPLACE(LTRIM(REPLACE(MATNR, '0', ' ')), ' ', '0') AS Material_ID,
SHKZG as Debe_haber,
BUDAT_MKPF as fecha_cont,
year(BUDAT_MKPF) as Año,
Month (BUDAT_MKPF) as Mes,
week (BUDAT_MKPF) as Semana,
year(BUDAT_MKPF)& Num(Month (BUDAT_MKPF),'00') as [Año_Mes],
year(BUDAT_MKPF)& Num(week (BUDAT_MKPF),'00') as [Año_Semana],
MENGE as Cantidad
FROM [(...)/MSEG.QVD]
(qvd);
LOAD
REPLACE(LTRIM(REPLACE(MATNR, '0', ' ')), ' ', '0') AS Material_ID,
MTART AS Tipo_Material
FROM [(...)/MARA.QVD]
(qvd);
LOAD
REPLACE(LTRIM(REPLACE(MATNR, '0', ' ')), ' ', '0') AS Material_ID,
SPRAS,
REPLACE(LTRIM(REPLACE(MATNR, '0', ' ')), ' ', '0') &' '& MAKTX AS Material_des
FROM [(...)/MAKT.QVD]
(qvd)
where SPRAS='S'
;
I am not certain which fields in that script correspond to those in your original post, so its hard to say.
you are right Jontydkpi,
Maybe checking this new post:
https://community.qlik.com/t5/New-to-Qlik-Sense/Stock-from-origin/m-p/1571122#M133177
Thanks!