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 5^{th} period backwards. The logic I used was;
IF(G2SUM(C3:G3)<=0,0,G2SUM(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
 vDate0 : =Num(Today())
 vDate92 : =Num(Today()92)
 vDate184 : =Num(Today()184)
 vDate276 : =Num(Today()276)
 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:
EMail: jonf@acumenbi.co.nz
Comments