Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
gasparbenito
New Contributor II

creating a new field

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

1 Solution

Accepted Solutions
MVP
MVP

Re: creating a new field

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,
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies
Channa
Valued Contributor III

Re: creating a new field

can you share sample data or APP

Channa
saminea_ANZ
Contributor

Re: creating a new field

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;

 

MVP
MVP

Re: creating a new field

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,
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
gasparbenito
New Contributor II

Re: creating a new field

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
New Contributor II

Re: creating a new field

Attached image
It explains better what I want to say.

Thank you!

MVP
MVP

Re: creating a new field

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
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
gasparbenito
New Contributor II

Re: creating a new field

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

MVP
MVP

Re: creating a new field

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
gasparbenito
New Contributor II

Re: creating a new field

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!