Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to create expression - availability

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.

ItemnumberStockRequestDate20121214201301072013010820130110201301112013011420130115201301162013011720130118
A2772 0000360007272
B3843 00000212102184
C140 00000000028
D0 0001084814412168072
E2160 0000024042012204
F6 4140000162603183672
G736 00000721242886052
H0 00000000108150
I348 37800009008412624

I need to reach this output in pivot table:

Itemnumberdelay2013011420130115201301162013011720130118
A273627362736273626642664
B384338223822384338223759
C140140140140140112
D-156-300-168-324-156-228
E216021362160174021481956
F-408-570-468-726-444-480
G736664612448676684
H0000-108-150
I-30-120-30-114-156-54

Can anybody help me to find right expression?

Thanks in advance.

1 Solution

Accepted Solutions
jonbrough
Valued Contributor

Re: How to create expression - availability

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

6 Replies
Not applicable

Re: How to create expression - availability

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

jonbrough
Valued Contributor

Re: How to create expression - availability

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

Not applicable

Re: How to create expression - availability

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.

jonbrough
Valued Contributor

Re: How to create expression - availability

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

Not applicable

Re: How to create expression - availability

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.

Not applicable

Re: How to create expression - availability

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

Community Browser