Discussion board where members can get started with QlikView.
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
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
its not clear. i have written the below script to get the stock
(biff, embedded labels, table is Sheet1$);
date(Max(CURDATE),'DD/MM/YYYY') as Date
resident Stock group By PARTNAME,YEAR,QUARTER;
WARHSQTY * COST1,
date(CURDATE,'DD/MM/YYYY') as Date,
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.
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 .
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,
thanks i will try it
On Thu, Oct 13, 2011 at 8:40 AM, saad khan <
I don't How to qty calculate .please explain.
the 4,16 direct enterd .please briefly explain
I dont understand what exactly u want to know. could you explain a bit more...