topic Stock Aging issue in QlikView

Stock Aging issue

gidon500 — Tue, 24 May 2011 14:04:06 GMT

Hello

My name is Gidon david ,

I need help and hope to get it here .

i am new to QV but made some progress , in the last few days I am tring to get Stock Value Aging of the Attached file

the file contains :

PARTNAME ( same as part number )

TRANS ( in the SQL transaction number )

WARHSQTY ( is the QTY we have in stock , you can see same number of each PN )

RCVQTY ( is the qty recv goods from supplier )

COST1 ( unit price cost )

AGE ( age in days )

CURDATE ( the excat date the good arrived to our warehouse )

the we have the year , qty, month .

my major problems is to sort the data as the information is hudge , you can see that the SQL give me to transactio and i need to sort it .

I want to get the value of each PN by year and qtr , :

581286-B21 left only 4 pcs the the total 4*1957.69= 7830.76 from 2011 , Q1

and for

581284-B21 left only 33 pcs so total is :

16 pcs from trans 743685 16*1309.46 = 43212.18 from 2011 , Q2

and 33-16 = 17 pcs from trans 725719 17*1523.22 = 25894.74 from 2011 , Q1

in the attached file i have added those calcualtion I hope I was clear

thanks

for helping

gidon

Stock Aging issue

deepakk — Tue, 24 May 2011 17:50:03 GMT

hi gidon,

its not clear. i have written the below script to get the stock

Stock:

LOAD PARTNAME,

WARHSQTY,

RCVQTY,

COST1,

CURDATE,

YEAR,

QUARTER,

MONTH

FROM

[Stock-Value-Aging.xls]

(biff, embedded labels, table is Sheet1$);

Load

PARTNAME,

date(Max(CURDATE),'DD/MM/YYYY') as Date

resident Stock group By PARTNAME,YEAR,QUARTER;

left join

LOAD PARTNAME,

WARHSQTY * COST1,

date(CURDATE,'DD/MM/YYYY') as Date,

YEAR,

QUARTER,

MONTH

FROM

[Stock-Value-Aging.xls]

(biff, embedded labels, table is Sheet1$);

drop table Stock;

The value for 581286-B21 is coming correct. But I am not sure about others.

can you check the same and let me know if you have any queries.

Stock Aging issue

gidon500 — Wed, 25 May 2011 06:33:23 GMT

Hello ,

Thanks for your promp and fast answer .

well it seems that i did not explain my self correct .

I will send new post will try to make it more clear .

Using more excel files to clarify my needs .

thanks

gidon

Stock Aging issue

Thu, 13 Oct 2011 06:40:29 GMT

Hey Gidon,

Better to use the following functions, then QV itself is smart enough to solve you problem.

Here "t_date" is the transaction date, in your case it is CURDATE ( the excat date the good arrived to our warehouse )

Day(t_date) AS CalendarDayOfMonth,

WeekDay(t_date) AS CalendarDayName,

Week(t_date) AS CalendarWeekOfYear,

Month(t_date) AS CalendarMonthName,

'Q' & Ceil(Month(t_date)/3) AS CalendarQuarter,

Year(t_date) AS CalendarYear,

Regards.

Re: Stock Aging issue

gidon500 — Thu, 13 Oct 2011 06:50:34 GMT

thanks i will try it

Gidon

On Thu, Oct 13, 2011 at 8:40 AM, saad khan <

Stock Aging issue

perumal_41 — Thu, 13 Oct 2011 07:12:34 GMT

Hi,

I don't How to qty calculate .please explain.

the 4,16 direct enterd .please briefly explain

QTY	COST	total cost
4	1957.69	7830.76


16	1309.46	20951.36
17	1523.22	25894.74
		46846.1

Stock Aging issue

Fri, 14 Oct 2011 04:58:59 GMT

I dont understand what exactly u want to know. could you explain a bit more...