topic Re: Stock value Daily in QlikView

Stock value Daily

Wed, 19 Mar 2014 09:13:32 GMT

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.

Re: Stock value Daily

MK_QSL — Wed, 19 Mar 2014 09:26:56 GMT

Like This?

Re: Stock value Daily

jonathandienst — Wed, 19 Mar 2014 09:28:34 GMT

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

Re: Re: Stock value Daily

Wed, 19 Mar 2014 09:58:12 GMT

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?

Re: Re: Stock value Daily

Wed, 19 Mar 2014 09:59:30 GMT

no i'm dealing with a transactions table

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

Re: Stock value Daily

MK_QSL — Wed, 19 Mar 2014 10:06:27 GMT

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

Re: Re: Re: Stock value Daily

qlikpahadi07 — Wed, 19 Mar 2014 10:18:33 GMT

Hi Mona,

you can Try this expression :

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

PFA

Re: Stock value Daily

maxgro — Wed, 19 Mar 2014 22:41:20 GMT

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

Re: Stock value Daily

qlikpahadi07 — Thu, 20 Mar 2014 05:28:36 GMT

was this helpful ?

Re: Stock value Daily

Thu, 20 Mar 2014 08:01:56 GMT

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.

Re: Stock value Daily

MK_QSL — Thu, 20 Mar 2014 08:05:24 GMT

Instead of SUM(CumulativeStock) us =CumulativeStock in expression.

Re: Stock value Daily

Thu, 20 Mar 2014 09:05:26 GMT

Oh, i didn't notice this reply. sorry for that.

i'm trying in it current now.

Thanks QlikPahadi07!

Re: Stock value Daily

qlikpahadi07 — Thu, 20 Mar 2014 10:18:30 GMT

It still didn't meet your requirement ??

Re: Re: Stock value Daily

Thu, 20 Mar 2014 11:15:33 GMT

No, as when i'm trying to display the product and date and qty

the values of the 2nd product y are not correct

the values should be

Product	Date	Qty
X	1/1/2010	100
X	3/1/2010	120
X	5/1/2010	220
X	6/1/2010	250
X	9/1/2010	260
Y	2/1/2010	15
Y	3/1/2010	215
Y	7/1/2010	235
Y	8/1/2010	385

Attached is the same file after trying to add what i'm talking about