topic Re: How to create expression - availability in QlikView

How to create expression - availability

Mon, 14 Jan 2013 09:27:00 GMT

Hi experts,

I hope that you can advice me with my task. I need to caluculate availability of items on the stock.

My input data are stock quantity and requested quantity per day.

Itemnumber	Stock	RequestDate	20121214	20130107	20130108	20130110	20130111	20130114	20130115	20130116	20130117	20130118
A	2772		0	0	0	0	36	0	0	0	72	72
B	3843		0	0	0	0	0	21	21	0	21	84
C	140		0	0	0	0	0	0	0	0	0	28
D	0		0	0	0	108	48	144	12	168	0	72
E	2160		0	0	0	0	0	24	0	420	12	204
F	6		414	0	0	0	0	162	60	318	36	72
G	736		0	0	0	0	0	72	124	288	60	52
H	0		0	0	0	0	0	0	0	0	108	150
I	348		378	0	0	0	0	90	0	84	126	24

I need to reach this output in pivot table:

Itemnumber	delay	20130114	20130115	20130116	20130117	20130118
A	2736	2736	2736	2736	2664	2664
B	3843	3822	3822	3843	3822	3759
C	140	140	140	140	140	112
D	-156	-300	-168	-324	-156	-228
E	2160	2136	2160	1740	2148	1956
F	-408	-570	-468	-726	-444	-480
G	736	664	612	448	676	684
H	0	0	0	0	-108	-150
I	-30	-120	-30	-114	-156	-54

Can anybody help me to find right expression?

Thanks in advance.

Re: How to create expression - availability

Mon, 14 Jan 2013 09:36:47 GMT

can u post the sample file......

Re: How to create expression - availability

Anonymous — Mon, 14 Jan 2013 09:45:41 GMT

This seems like an accumulation of a stock balance to me, based upon Stock movements.

Your expression can test if it is the first row (date dimension):

- if it is then use a sum of the prior stock movements. I have found it easiest to calculate this using a variable, outside of the chart

- if it is not the first row, then you can accumulate by finding the above row and adding your new value.

An expression could be something like this:

if(RowNo()=1,$(ItemStatusAmount),Above(sum(StockMovement))+sum(StockMovement))

ItemStatusAmount could be calculated using a formula like:

sum(ALL if(TransDate<min(TransDate),StockMovement))

Jonathan

Re: How to create expression - availability

Mon, 14 Jan 2013 10:21:34 GMT

hi, thanks for your suggestion, that are not transactions, but not delivered customer orders, but I think formula is the same. I need to see, when the product will not be avalaible on stock.

I'll try to post qv and excel cal as well. I'm not sure with variables which contain formula at all.

Re: How to create expression - availability

Anonymous — Mon, 14 Jan 2013 11:20:44 GMT

Understood about this summing upcoming order values, though as I understand it this is so as to predict the likely stock levels.

See attached for a way of doing this using the above() function. There was no need for a seperate variable as you have the starting stock figure. I have not managed to get working with the pivoting though.

Jonathan

Re: How to create expression - availability

Mon, 14 Jan 2013 11:59:59 GMT

Thank you very much, it is what I was looking for. I changed expression slightly:

if(ColumnNo()=1,only(Stock)-sum(Nedodane),Before(Aggregate)-sum(Nedodane))

and now it is working as I wanted. Thank you very much.

Now I need to find a way have to cumulute all not nedelivered orders in previous dates.

Re: How to create expression - availability

Mon, 14 Jan 2013 12:58:47 GMT

Jonathan thank you very much for your help and quick response. It's almost perfect right now.