Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
Hi
thanks i will try it
Gidon
On Thu, Oct 13, 2011 at 8:40 AM, saad khan <
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 | |||
I dont understand what exactly u want to know. could you explain a bit more...