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: 
Not applicable

Standard Deviation of Warehouse Movements

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

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

10 Replies
effinty2112
Master
Master

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

Not applicable
Author

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.

effinty2112
Master
Master

Hi Michele,

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

cheers

Andrew

effinty2112
Master
Master

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

ruanhaese
Partner - Creator II
Partner - Creator II

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/StatisticalAggregationFu...

Not applicable
Author

I agree with you Ruan!!

I think that your solution is perfect for a User that want to do many selections but mine must only take a look on this report.

He selects only the item and looks the analysis on it.

I want to make an easy report that has only a few field (maybe one) changeable.

Not applicable
Author

yes of course,

i made an expression firstly only to check the accuracy of the result.

Now I do a Reference line!!!

Not applicable
Author

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.

effinty2112
Master
Master

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