# QlikView Documents

Documents for QlikView related information.

# Ageing Stock Analysis in Qlik Sense

New Contributor II

## Ageing Stock Analysis in Qlik Sense

I was recently set the task to create a table that looked at Ageing Stock for one of my clients. It required looking at the date, Qty and Location of stock ‘In and outs’ on and First in First Out (FIFO) basis, and then producing values for date period buckets.

The finished tables looked like this

The first challenge was to work out the logic of the expressions, for this I used Excel. I created a dummy table with fake data to work out the equation for each bucket. The excel table looked like this:

I first worked out what number should be in each of the period balance cells, then set out to work out the expression for each starting at the 5th period backwards. The logic I used was;

IF(G2-SUM(C3:G3)<=0,0,G2-SUM(C3:G3))

IF the Purchase amount for period 5  (G2)

is less than the sum of all sales (SUM(C3:G3)) Then 0

ELSE Purchase amount (G2) minus the sum of all sales (SUM(C3:G3))

The expressions for Period 1- 4 were different to 5 but similar to each other. Period 1 calculation shown below.

IF(SUM(D2:G2)-SUM(\$C3:\$G3) <=0,

IF(SUM(C2:\$G2)-SUM(\$C3:\$G3)<=0,0,

IF(SUM(C2:\$G2)-SUM(\$C3:\$G3) < C2,

(SUM(C2:\$G2)-SUM(\$C3:\$G3)),C2 )),C2)

IF the sum of previous purchases (SUM(D2:G2)) minus the sum of all sales (SUM(\$C3:\$G3)) is less than or equal 0

Then IF  the sum of all purchases (SUM(C2:\$G2)) minus the sum of all sales (SUM(\$C3:\$G3)) is less than or equal to 0 then 0

Else IF the sum of all purchases (SUM(C2:\$G2))  minus the sum of all sales (SUM(\$C3:\$G3)) is less than the current period Purchases (C2)

Then the sum of all purchases (SUM(C2:\$G2))  minus the sum of all sales (SUM(\$C3:\$G3))

Else sum of current Purchases (C2)

Once I had double checked the equations and checked they worked with all different permutations I then went about creating them in Qlik

In the final table the Periods were

• 0 - 92 Days
• 93 – 184 Days
• 185 – 276 Days
• 276 – 365 Days
• 365+ Days

Here are the steps I followed to replicate in Qlik Sense:1) Create Variables for each of the periods as follows

1. vDate0 : =Num(Today())
2. vDate92 : =Num(Today()-92)
3. vDate184 : =Num(Today()-184)
4. vDate276 : =Num(Today()-276)
5. vDate365+ : =Num(Today()-365)

2) Create a Master Item Expression for each date periodThe Master Item Expression for 365+ Days corresponded with period 5 in excel and looked like this

IF(Sum({< [RECEIPT] = {'Out'} , [DATE] = {"<\$(vDate365+)"} >} QTY)

-

Sum({< [RECEIPT]  = {'In'} >} QTY) <= 0,

0,

IF(Sum({< [RECEIPT] = {'Out'} , [DATE] = {"<\$(vDate365+)"} >} QTY)

-

Sum({< [RECEIPT]  = {'In'} >} QTY))

The expressions for the other time periods were as follows (below for 0 – 92):

IF(

Sum({<[RECEIPT] = {'In'} , [DATE] = {"<\$(vDate92)"} >} QTY)

+

Sum({<[RECEIPT] = {'Out'} >} QTY)

<=0

,

IF(

Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=\$(vDate0)"} >} QTY)

+

Sum({<[RECEIPT] = {'Out'} >} QTY)

<= 0,0

,

IF(

Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=\$(vDate0)"} >} QTY)

+

Sum({<[RECEIPT] = {'Out'} >} QTY)

<

Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=\$(vDate0) >=\$(vDate92)"} >} QTY),

Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=\$(vDate0)"} >} QTY)

+

Sum({<[RECEIPT] = {'Out'} >} QTY),

Sum({<[RECEIPT] = {'In'} , [DATE] = {"<=\$(vDate0) >=\$(vDate92)"} >} QTY)))

,

Sum({<[RECEIPT] = {'Out'} ,  [DATE] = {"<=\$(vDate0) >=\$(vDate92)"} >} QTY))

As you copy the expression to the next date period master items you just need to change the Variables to the next date period variable, for example.

<=\$(vDate0) becomes <\$(vDate92)

<\$(vDate92) becomes <\$(vDate184)

<\$(vDate184) becomes <\$(vDate276)

<\$(vDate276) becomes <\$(vDate365+)

3) To finish the project off, insert a Table object onto your sheet, drag in the Location field, then drag in each master items starting with 0 - 92 Days.

If you have any questions or comments please feel free to get in touch with me:

Jon Foote is a Senior Qlik Consultant with Acumen BI and can be contacted at:

E-Mail: jonf@acumenbi.co.nz

Tags (1)
Labels (4)

• ### Technical Brief

Contributor

IF(G2-SUM(C3:G3)<=0,0,G2-SUM(C3:G3)) here G2 is purchase which I suppose is Stock in

So below formulas should be updated for In to Out and vise versa

IF(Sum({< [RECEIPT] = {'Out'} , [DATE] = {"<\$(vDate365+)"} >} QTY)

-

Sum({< [RECEIPT]  = {'In'} >} QTY) <= 0,

0,

IF(Sum({< [RECEIPT] = {'Out'} , [DATE] = {"<\$(vDate365+)"} >} QTY)

-

Sum({< [RECEIPT]  = {'In'} >} QTY))

New Contributor II

Hi Fawaz

Yes G2 is Stock In. I'm not sure what you are asking about the second formula. It does depend on your data, in this case we had one field for Stock In and Stock Out, if you are reading Stock In from a different field than Stock Out, you will need to adjust the Set expression to suit your fields.

Does that help?

Regards

Jon

Contributor

yeah , I think it should be my data.

Anyways the explanation with excel data was very helpful.

Version history
Revision #:
1 of 1
Last update:
‎03-28-2017 08:13 PM
Updated by: