topic Re: Calculate StockInHand in QlikView

Calculate StockInHand

Thu, 19 Feb 2015 10:55:15 GMT

Hi,

Can any one please help me, on "how to calculate Stock In Hand".

Below table is from excel.

date	StockIN	StockOUT	StockINH	LogicExplanation
01/01/15	100	15	85	100 - 15 = 85
01/02/15	10	45	50	85 + 10 - 45 = 50
01/03/15	1	11	40	50 + 1 - 11 = 40
01/04/15	25	35	30	40 + 25 - 35 = 30
01/05/15	20	10	40	30 + 20 - 10 = 40

How to get the values in "StockINH" using qlikview.

I'm using personal edition, please tell me the procedure to achieve.

Thanks In Advance.

Regards,

Pavan.

Re: Calculate StockInHand

rubenmarin — Thu, 19 Feb 2015 11:02:53 GMT

Hi Pavan, in StockINH expression you can use:

Alt(Above(TOTAL [StockINH]), 0) + StockIN - StockOUT

Re: Calculate StockInHand

arulsettu — Thu, 19 Feb 2015 11:05:43 GMT

try this

sum(StockIN)-sum(StockOUT)

Re: Calculate StockInHand

Thu, 19 Feb 2015 11:09:52 GMT

Hi Ruben,

Thanks for your quick response.

Out of 5 columns, only first 3(date, StockIN, StockOUT) are in excel, last 2, I have created for your reference.

I need to achieve StockINH, for that last column is the logic.

Regards,

Pavan.

Re: Calculate StockInHand

arulsettu — Thu, 19 Feb 2015 11:12:08 GMT

create a straight table and date as dimension use above code in script

Re: Calculate StockInHand

rubenmarin — Thu, 19 Feb 2015 11:12:16 GMT

Yes, I know that, please check attachment.

Re: Calculate StockInHand

rubenmarin — Thu, 19 Feb 2015 11:14:00 GMT

OK, you have personal edition... what I did:

Script:

LOAD Date(date) as date, StockIN, StockOUT;

LOAD * INLINE [

date,StockIN,StockOUT

01/01/15,100,15

01/02/15,10,45

01/03/15,1,11

01/04/15,25,35

01/05/15,20,10

];

Graph:

Dimensions: date,StockIN,StockOUT

Expression StockINH: Alt(Above(TOTAL [StockINH]), 0) +StockIN-StockOUT

Re: Calculate StockInHand

Thu, 19 Feb 2015 11:28:47 GMT

Hi Ruben,

I don't have stockINH column in excel, it is showing error.

Please let me know where I'm doing wrong, please help me out.

Regards,

Pavan.

Re: Calculate StockInHand

rubenmarin — Thu, 19 Feb 2015 11:31:42 GMT

That's not supposed to be a field name, it's a expression name, if you name your expression "StockINH", it will work. If you named it different you need to set your expression name:

Expression: Alt(Above(TOTAL [ExpressionName]), 0) +StockIN-StockOUT

Re: Calculate StockInHand

Thu, 19 Feb 2015 11:40:15 GMT

Hi Ruben,

Thanks a ton for your shift help.

You really deserve it.

Thank you so much.

Regards,

Pavan.

Re: Calculate StockInHand

Thu, 19 Feb 2015 12:36:40 GMT

Thanks Arul,

Thanks for your help.

Re: Calculate StockInHand

mukesh24 — Thu, 19 Feb 2015 14:49:19 GMT

Hi Pavan,

Instead of calculating on front end, calculate it on backend ... Because performance increase if u calculate at backend.. U can do these as:-

FD:

LOAD * INLINE [

date, StockIN, StockOUT

01-01-2015, 100, 15

01-02-2015, 10, 45

01-03-2015, 1, 11

01-04-2015, 25, 35

01-05-2015, 20, 10

];

FDT:

load Year(date(date,'YYYYMMDD')) as Year,

StockIN - StockOUT as InHand,

Resident FD order by date;

FD1: load

if(Year <> peek(Year),StockIN - StockOUT,peek(CB)+(InHand)) as CB,

Resident FDT;

drop table FD,FDT;

Regards,

Mukesh Chaudhari