Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gidon500
Creator II
Creator II

Stock Aging issue

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

6 Replies
deepakk
Partner - Specialist III
Partner - Specialist III

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
Creator II
Creator II
Author

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

Not applicable

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
Creator II
Creator II
Author

Hi

thanks i will try it

Gidon

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

perumal_41
Partner - Specialist II
Partner - Specialist II

Hi,

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

the 4,16 direct enterd .please briefly explain

                                                               
QTYCOSTtotal  cost
41957.697830.76
161309.4620951.36
171523.2225894.74
46846.1
Not applicable

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