18 Replies Latest reply: Dec 18, 2014 6:46 AM by Jagan Nalla RSS

    Average inventory

    Marco van Zand

      Dear qlikviewer,

       

      How to calculate the average inventory in qlikview. please the table

       

      ItemnrDateSerialnrWarehousenrMutationInventoryOldInventoryNew
      11-12-201401-51510
      115-11-20141121315
      115-11-201402-220
      110-11-20140110313
      11-11-201412202
      11-11-201401303

      Extra info: there are a lot of itemnr

       

      Who could help me?

        • Re: Average inventory
          Gysbert Wassenaar

          rangesum(Firstsortedvalue(InventoryOld, Date),Firstsortedvalue(InventoryNew,-Date))/2

            • Re: Average inventory
              Marco van Zand

              What do you mean with Firstsortedvalue

                • Re: Average inventory
                  James Summerson

                  From HELP (F1)

                   

                  firstsortedvalue( [{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

                   

                  returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

                   

                  Examples:
                  firstsortedvalue ( PurchasedArticle, OrderDate )     

                  firstsortedvalue ( PurchasedArticle, -OrderDate, 2 )     

                  firstsortedvalue ( A/B, X*Y/3 )    

                  firstsortedvalue ( distinct PurchasedArticle, OrderDate )     

                  firstsortedvalue ( total PurchasedArticle, OrderDate )    

                  firstsortedvalue ( total <Grp> PurchasedArticle, OrderDate )   

              • Re: Average inventory
                anbu cheliyan

                Do you want to calculate average of inventory for each itemnr monthly or weekly or entire input?

                 

                Itemnr, Avg(InventoryNew)

                1,43/6

                • Re: Average inventory
                  Marco Wedel

                  can you specify the calculation of the average inventory for your sample data?

                  • Re: Average inventory
                    Marco van Zand

                    I attached the Qlikviewfile.

                     

                    The correct answer Qlikview has to calculate is

                    ItemnrDateSerialnrWarehousenrMutationInventoryOldInventoryNewColumnXColumnY
                    11-12-201401-5151016240
                    115-11-20141121315565
                    115-11-201402-2201428
                    110-11-20140110313927
                    11-11-20141220200
                    11-11-20140130300

                     

                    Column X: DateRow - DateRowPrevious WHERE warehousenr is equal

                    Column Y: Column X * InventoryOld

                      • Re: Average inventory
                        Jagan Nalla

                        Hi,

                         

                        Can you explain little bit more on Column X calculation. At least for two or three rows.

                         

                        Thanks,

                        Jagan

                          • Re: Average inventory
                            Marco van Zand

                            Sure,

                             

                            Column X is like match and index

                             

                            I look for the next rows when itemnr is equal and warehousenr is equal

                             

                            ItemnrDateSerialnrWarehousenrMutationInventoryOldInventoryNewColumnXColumnY
                            11-12-201401-5151016 (1-12-2014 - 15-11-2014)240
                            115-11-201411213155 (15-11-2014 - 10-11-2014)65
                            115-11-201402-22014 (15-11-2014 - 1-11-2014)28
                            110-11-201401103139 (10-11-2014 - 1-11-2014)27
                            11-11-20141220200
                            11-11-20140130300
                              • Re: Average inventory
                                Jagan Nalla

                                That's fine. But how are you getting the values 16,5,14,9. According to you 16(from inventorynew 10-15 is -5). We need the information from which columns you are getting the values.

                                • Re: Average inventory
                                  Gysbert Wassenaar
                                  Temp:
                                  LOAD * INLINE [
                                      Itemnr, Date, Serialnr, Warehousenr, Mutation, InventoryOld, InventoryNew
                                      1, 1-12-2014, 0, 1, -5, 15, 10
                                      1, 15-11-2014, 1, 1, 2, 13, 15
                                      1, 15-11-2014, 0, 2, -2, 2, 0
                                      1, 10-11-2014, 0, 1, 10, 3, 13
                                      1, 1-11-2014, 1, 2, 2, 0, 2
                                      1, 1-11-2014, 0, 1, 3, 0, 3
                                  ];
                                  
                                  Result:
                                  LOAD *, ColumnX * InventoryOld as ColumnY;
                                  LOAD *, if(Itemnr&'|'&Warehousenr=previous(Itemnr&'|'&Warehousenr) ,rangesum(Date,-previous(Date)),0) as ColumnX
                                  Resident Temp
                                  Order by Itemnr, Warehousenr, Date asc;
                                  
                                  drop table Temp;
                                  

                                   

                                  See attached qvw.

                              • Re: Average inventory
                                Marco Wedel

                                Hi,

                                 

                                one solution could be:

                                 

                                QlikCommunity_Thread_146100_Pic1.JPG

                                 

                                tabInvent:
                                LOAD *
                                FROM [http://community.qlik.com/thread/146100] (html, codepage is 1252, embedded labels, table is @1);
                                
                                Left Join (tabInvent)
                                LOAD *,
                                    ColumnX * InventoryOld as ColumnY;
                                LOAD Itemnr,
                                    Warehousenr,
                                    Date,
                                    InventoryOld,
                                    If(Itemnr=Previous(Itemnr) and Warehousenr=Previous(Warehousenr),Date-Previous(Date),0) as ColumnX   
                                Resident tabInvent
                                Order By Itemnr, Warehousenr, Date;
                                

                                 

                                hope this helps

                                 

                                regards

                                 

                                Marco

                                  • Re: Average inventory
                                    Marco van Zand

                                    This is the original script

                                     

                                     

                                    SET ThousandSep=',';
                                    SET DecimalSep='.';
                                    SET MoneyThousandSep=',';
                                    SET MoneyDecimalSep='.';
                                    SET MoneyFormat='$#,##0.00;($#,##0.00)';
                                    SET TimeFormat='h:mm:ss TT';
                                    SET DateFormat='D/M/YYYY';
                                    SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
                                    SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
                                    SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

                                    ODBC CONNECT32 TO VHP ;

                                    SQL SELECT Aantal-mut as Mutation,
                                    Aantal-nieuw as InventoryNew,
                                    Aantal-oud as InventoryOld,
                                    Artikelnr as Itemnr,
                                    Datum as Date,
                                    Magazijnnr as Warehousenr,
                                    Volgnummer as Serialnr
                                    FROM VOV_Voorraad_verslag WHERE subsystem = 0 ;

                                     

                                    How to put your script in this script?

                                      • Re: Average inventory
                                        Marco Wedel

                                        Just replace my first load (tabInvent) with your SQL select and name it tabInvent: also.

                                         

                                        Hope this helps

                                         

                                        Regards

                                         

                                        Marco

                                          • Re: Average inventory
                                            Marco van Zand

                                            Almost.... there is a problem when:

                                            - Date is equal

                                            - Warehousenr is equal

                                            - Multiple serialnr's

                                             

                                             

                                            Date Itemnr Serialnr Warehousenr InventoryOld Mutation InventoryNew Days DaysInventory Days has to be
                                            17-Apr-14 405015 1 1 7 -3 4 27 189 0
                                            17-Apr-14 405015 0 1 4 3 7 0 0 27
                                            21-Mar-14 405015 1 1 5 -1 4 1 5 0
                                            21-Mar-14 405015 0 1 14 -9 5 0 0 1
                                            20-Mar-14 405015 1 3 0 6 6 0 0 0
                                            20-Mar-14 405015 0 1 20 -6 14 344 6880 344