Stock value Daily

[#1]

Hi All,

i have a table with these columns Area,Country,Product,Date,Quantity

it's  a transactions table i want to evaluate the stock every day and for the missing dates in the table

how to do that?

Thanks.

[MK_QSL]

Like This?

[jonathandienst]

Hi

I suggest that you add a master calendar that includes all the dates from earliest to latest transaction. Keep it as a separate table, and associate the transaction Date and calendar Date (by giving them the same name).

There are many links if you search here on "master calendar".

HTH

Jonathan

[#4]

not totally like it

the table i have is a transactions table like this

Area	Branch	Product	Date	Qty
A	A1	X	1/1/2010	100
A	A2	Y	3/1/2010	200
B	B1	X	5/1/2010	100
A	A3	Y	8/1/2010	150
B	B2	X	6/1/2010	30
C	C1	Y	7/1/2010	20
A	A1	X	9/1/2010	10
B	B2	Y	2/1/2010	15
C	C2	X	3/1/2010	20

I want the result to be

Area	Branch	Product	Date	Qty
A	A1	X	1/1/2010	100
A	A1	X	9/1/2010	110

And so on, @ 9/1/2010 for the same product the quantity which is 10 is added to the start quantity of that product which is 100

any help?

[#5]

no i'm dealing with a transactions table

i want to add the transactions for a product to evaluate the stock every day

[MK_QSL]

Like this?

Temp:

Load *, Area&Branch&Product as Key Inline

[

  Area, Branch, Product, Date, Qty

  A, A1, X, 1/1/2010, 100

  A, A2, Y, 3/1/2010, 200

  B, B1, X, 5/1/2010, 100

  A, A3, Y, 8/1/2010, 150

  B, B2, X, 6/1/2010, 30

  C, C1, Y, 7/1/2010, 20

  A, A1, X, 9/1/2010, 10

  B, B2, Y, 2/1/2010, 15

  C, C2, X, 3/1/2010, 20

];

Final:

Load

  Area,

  Branch,

  Product,

  Date,

  Qty,

  IF(Key = Previous(Key), Peek(CumulativeStock)+Qty, Qty) as CumulativeStock

Resident Temp

Order By Area,Branch,Product, Date;

Drop Table Temp;

Update  : Date added in Order By

[qlikpahadi07]

Hi Mona,

you can Try this expression :

sum(aggr(sum(Qty)+ RangeSum(above(sum(Qty),1,RowNo()-1)),Area,Branch,Product,Date))

PFA

[maxgro]

good the idea to use

Area&Branch&Product as Key

to avoid a lot of peek

with real data you probably need to add the Date at the end of Order By Area,Branch,Product

[qlikpahadi07]

was this helpful ?

[#10]

thanks Manish,

but when i try to make a straight table with Product and Date as dimensions and sum(CumulativeStock) as the expression the result i supposed to be the summation of the quantity for product x in day 1/1/2010 for all the areas and branches but the result was not as i supposed.

i.e i want it to be flexible to sum cumulativestock for any dimension i want.