10 Replies Latest reply: Jul 20, 2017 11:18 AM by Michele Pierobon RSS

    Standard Deviation of Warehouse Movements

    Michele Pierobon

      Hi Qlikers,

      i write to ask you a solution to calculate the Standard Deviation of Warehouse Movements on script.

      It could be interesting to have the standard deviation of the warehouse movements to find the safety stock about an item.

       

      I attach a simple example to be clear about my goal.

      SCRIPT

      WAREHOUSE_MOVEMENTS:

      Load * Inline

      [     ITEM,      MOVEMENT_DATE,      MOVEMENT_TYPE,      MOVEMENT_QTY

                A,          '07/05/2016',                    LOAD,                         10

                A,          '27/05/2016',                    LOAD,                          2

                A,          '17/05/2016',                    UNLOAD,                    -13

                A,          '12/05/2016',                    LOAD,                         17

                A,          '10/06/2016',                    UNLOAD,                     -2

                A,          '05/06/2016',                    UNLOAD,                     -20

                A,          '13/06/2016',                    LOAD,                         30

                A,          '27/06/2016',                    UNLOAD,                    -12

                A,          '15/07/2016',                    LOAD,                         50

                A,          '02/07/2016',                    UNLOAD,                    -50

                A,          '13/07/2016',                    LOAD,                         6

                A,          '25/07/2016',                    UNLOAD,                    -11

                A,          '05/08/2016',                    UNLOAD,                    -32

                A,          '12/10/2016',                    LOAD,                         27

                A,          '07/01/2017',                    UNLOAD,                    -5

                A,          '17/03/2017',                    UNLOAD,                    -25

                A,          '22/04/2017',                    UNLOAD,                    -2

                A,          '30/05/2017',                    LOAD,                         40

      ];

       

      Thanks in advance!!

        • Re: Standard Deviation of Warehouse Movements
          Andrew Walker

          Hi Michele,

          Try:

           

          StdEv:

          LOAD

          ITEM,

          MOVEMENT_TYPE,

          Stdev(MOVEMENT_QTY) as StDev

          Resident WAREHOUSE_MOVEMENTS Group by ITEM, MOVEMENT_TYPE;

           

          Result:

           

          ITEM MOVEMENT_TYPE StDev
          ALOAD16.976875
          AUNLOAD15.120259

           

          Remove MOVEMENT_TYPE from the above srcipt id you don't want to differentiate between movement types.


          Cheers


          Andrew

            • Re: Standard Deviation of Warehouse Movements
              Michele Pierobon

              Thanks Andrew!!

              I made two types of deviation standard.

               

              WAREHOUSE_MOVEMENTS1:

              Load * Inline

               

              [ITEM, MOVEMENT_DATE, MOVEMENT_TYPE, MOVEMENT_QTY

               

              A,'07/05/2016',LOAD,10

              A,'27/05/2016',LOAD,2

              A,'17/05/2016',UNLOAD,-13

              A,'12/05/2016',LOAD,17

              A,'10/06/2016',UNLOAD,-2

              A,'05/06/2016',UNLOAD,-20

              A,'13/06/2016',LOAD,30

              A,'27/06/2016',UNLOAD,-12

              A,'15/07/2016',LOAD,50

              A,'02/07/2016',UNLOAD,-50

              A,'13/07/2016',LOAD,6

              A,'25/07/2016',UNLOAD,-11

              A,'05/08/2016',UNLOAD,-32

              A,'12/10/2016',LOAD,27

              A,'07/01/2017',UNLOAD,-5

              A,'17/03/2017',UNLOAD,-25

              A,'22/04/2017',UNLOAD,-2

              A,'30/05/2017',LOAD,40

               

              ];

               

              WAREHOUSE_MOVEMENTS:

              LOAD

              ITEM,

              MOVEMENT_DATE,

              MOVEMENT_TYPE,

              MOVEMENT_QTY,

              ITEM&'|'&MOVEMENT_TYPE AS KEY_IT_MOVTY

              RESIDENT WAREHOUSE_MOVEMENTS1;

              DROP TABLE WAREHOUSE_MOVEMENTS1;

               

              LEFT JOIN(WAREHOUSE_MOVEMENTS)

              LOAD

              ITEM&'|'&MOVEMENT_TYPE AS KEY_IT_MOVTY,

              Stdev(MOVEMENT_QTY) as STDEV_MOVTY

              Resident WAREHOUSE_MOVEMENTS Group by ITEM, MOVEMENT_TYPE;

               

              LEFT JOIN(WAREHOUSE_MOVEMENTS)

              LOAD

              ITEM,

              Stdev(MOVEMENT_QTY) as STDEV

              Resident WAREHOUSE_MOVEMENTS Group by ITEM;

               

              The result is this.

                • Re: Standard Deviation of Warehouse Movements
                  Andrew Walker

                  Hi Michele,

                  Glad to help and thanks for showing your chart, that's a nice visualisation.

                   

                  cheers

                   

                  Andrew

                    • Re: Standard Deviation of Warehouse Movements
                      Michele Pierobon

                      Hi Andrew,

                      I need a suggestion for another problem linked with the previous one.

                      I have to calculate standard deviation considering a MonthName aggregation.

                      Unfortunately not each month has consumptions but i need to consider all months to calculate a correct standard deviation.

                       

                       

                      Example:

                       

                      The StDev function of Qlik consider only the months with a value (3 in this example) to calculate the standard deviation.

                      How can i do on script?

                       

                      Thanks in advance.

                        • Re: Standard Deviation of Warehouse Movements
                          Andrew Walker

                          Hi Michele,

                          I would tackle it something like this:

                           

                          Temp:

                          Load

                          Month,

                          Sum( ... ) //monthly consumption

                          From ...

                          Group by Month;

                           

                          MappingConsumption:

                          Mapping Load * Resident Temp;

                           

                           

                          Drop Table Temp;

                           

                           

                          If you have a table of calendar months (Months) then:

                           

                          Left Join(Months)

                          Load

                          Month,

                          ApplyMap('MappingConsumption',Month,0) as Consumption

                          Resident Months;


                          The mapping table will not have any rows for months with no consumption but we are rescued by the ApplyMap's default parameter, in this case 0 so these months will receive a zero value rather than remain null.


                          Good Luck


                          Andrew

                            • Re: Standard Deviation of Warehouse Movements
                              Michele Pierobon

                              Good idea but i need keeping linked the fields ITEM, ITEM DESCRIPTION with MONTHS and CONSUMPTION.

                              The previous piece of script is the following:

                               

                              Warehouse_Movements:

                              LOAD

                                   [Item Code],

                                   [Item Description],

                                   [Movement Date],

                                   monthname([Movement Date]) AS [Movement Month],

                                   CM,

                                   [Movement Description],

                                   [Movement Qty],

                                   Stock

                              FROM

                              [U:\Warehouse Movements.xls]

                              (biff, embedded labels, table is Sheet1$)

                              where [Movement Qty]<0;


                              I have to keep ITEM CODE and ITEM DESCRIPTION linked with MONTHS and MONTHLY CONSUMPTION.

                              It is a problem because your solution use a mapping table that allow only two column.

                              How can i solve this problem?


                              I can't do many mapping table because MONTHS aren't a key field (ITEM is it).

                        • Re: Standard Deviation of Warehouse Movements
                          Andrew Walker

                          Hi Michelle,

                          You might like to look into adding Reference Lines to a chart to show your standard deviation. Go to the Presentation tab and add two reference lines for ±stdev(MOVEMENT_QTY).

                           

                          1.jpg

                      • Re: Standard Deviation of Warehouse Movements
                        Ruan Haese

                        You can also use the StDev function in your expressions and get the same result.

                        In this case adding a table with Dimension Movement_Type and an expression

                        =StDev( MOVEMENT_QTY )

                        would yield the same result, and would allow for the selections,

                        whereas in the script its not as versatile on the selections.

                         

                        https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/ChartFunctions/StatisticalAggregationFunctionsinCharts/stde…