Stock Aging issue

[gidon500]

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 , :

like

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

[deepakk]

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.

[gidon500]

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

[#4]

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.

[gidon500]

Hi

thanks i will try it

Gidon

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

[perumal_41]

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

[#7]

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