creating a new field

[gasparbenito]

Hi all, I have a table with these data:

• Material
• Date
• Quantity
• Debit/Credit (if the field is Debit, the quantity is negative and if it is Credit, the quantity is positive), but the table always shows the amount in Positive.

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

[Channa]

can you share sample data or APP

[saminea_ANZ]

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;

[jonathandienst · Accepted solution]

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,

[gasparbenito]

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!

[gasparbenito]

Attached image
It explains better what I want to say.

Thank you!

[jonathandienst]

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

[gasparbenito]

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

[jonathandienst]

I am not certain which fields in that script correspond to those in your original post, so its hard to say.

[gasparbenito]

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!