Standard Deviation of Warehouse Movements

[#1]

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!!

[#2]

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

(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).