6 Replies Latest reply: Oct 14, 2011 12:58 AM by saad khan RSS

    Stock Aging issue

    gidon david

      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

        • Stock Aging issue
          Deepak Kurup

          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
              gidon david

              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

                  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.