Sum Data based on previous records

[ksharpes]

Hello,

I wish to make the following calculation 

Stock qty \ (Sales qty (over last 12 months) / (amount of months)) as [Coverage]

The table is as below, and "Reporting Date" is the last day of the month.

[Coverage]:
LOAD
Item_ID,
"Stock Qty",
"Sales Qty (Ext)",
"Sales Qty (All)",
"Reporting Date"

FROM [lib://Qlik Data Folder /Data_Values.QVD](qvd)

Data is formatted like the below

Item_ID	Sum([Stock Qty])	Sum([Sales Qty (All)])	Sum([Sales Qty (Ext)])	Reporting Date	Coverage
Item 1	90	10	10	31/10/2018	9
Item 1	100	10	10	30/09/2018	10
Item 1	110	10	10	31/08/2018	11
Item 1	120	10	10	31/07/2018	12
Item 1	130	10	10	30/06/2018	13
Item 1	140	10	10	31/05/2018	14
Item 1	150	10	10	30/04/2018	15
Item 1	160	10	10	31/03/2018	16
Item 1	170	10	10	28/02/2018	17
Item 1	180	10	10	31/01/2018	18
Item 1	190	10	10	31/12/2017	19
Item 1	200	10	10	30/11/2017	20
Item 1	210	10	10	31/10/2017	21
Item 1	220	10	10	30/09/2017	22
Item 1	230	10	10	31/08/2017	23
Item 1	240	10	10	31/07/2017	24
Item 1	250	10	10	30/06/2017	25
Item 1	260	10	10	31/05/2017	26
Item 1	270	10	10	30/04/2017	27
Item 1	280	10	10	31/03/2017	28
Item 1	290	10	10	28/02/2017	29
Item 1	300	10	10	31/01/2017	30
Item 2	90	10	10	31/10/2018	9
Item 2	100	10	10	30/09/2018	10
Item 2	110	10	10	31/08/2018	11
Item 2	120	10	10	31/07/2018	12
Item 2	130	10	10	30/06/2018	13
Item 2	140	10	10	31/05/2018	14
Item 2	150	10	10	30/04/2018	15
Item 2	160	10	10	31/03/2018	16
Item 2	170	10	10	28/02/2018	17
Item 2	180	10	10	31/01/2018	18
Item 2	190	10	10	31/12/2017	19
Item 2	200	10	10	30/11/2017	20
Item 2	210	10	10	31/10/2017	21
Item 2	220	10	10	30/09/2017	22
Item 2	230	10	10	31/08/2017	23
Item 2	240	10	10	31/07/2017	24
Item 2	250	10	10	30/06/2017	25
Item 2	260	10	10	31/05/2017	26
Item 2	270	10	10	30/04/2017	27
Item 2	280	10	10	31/03/2017	28
Item 2	290	10	10	28/02/2017	29
Item 2	300	10	10	31/01/2017	30

[Kushal_Chawda]

can you give one example?

[ksharpes]

The result i am looking to achieve is the "coverage" column.

[Kushal_Chawda]

What is formula? It's not clear to me. 

[ksharpes]

So if we were looking at data from the end of December it would be:

Stock Qty (at end of December) / ( Sales Qty (each month in 2019) / 12)

or simply: Stock Qty / Average Monthly Sales in past 12 months

[Kushal_Chawda]

In chart, you can use below expression. Make sure that Report_Date is sorted Ascending.

=sum(StockQty)/Above(total sum(SalesQty),0,12)*avg(1)

[ksharpes]

I wish to perform this task within Script, as i would like to categorize this further and use that as a dimension.

[Kushal_Chawda · Accepted solution]

may be this

Data:
LOAD
    Item_ID,
    "Sum([Stock Qty])" as StockQty,
    "Sum([Sales Qty (All)])" as SalesQty,
    "Sum([Sales Qty (Ext)])" as SalesQtyExt,
    "Reporting Date"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

Final:
Load *,
     StockQty/Avg_12_Month_Sales as Coverage;
Load *,
     RangeSum(if(Item_ID<>Previous(Item_ID),SalesQty,0), 
     if(Item_ID=Peek('Item_ID'),Peek('SalesQty', -1),0), if(Item_ID=Peek('Item_ID',-1),Peek('SalesQty', -1),0), 
     if(Item_ID=Peek('Item_ID',-2),Peek('SalesQty', -2),0), if(Item_ID=Peek('Item_ID',-3),Peek('SalesQty', -3),0), 
     if(Item_ID=Peek('Item_ID',-4),Peek('SalesQty', -4),0), if(Item_ID=Peek('Item_ID',-5),Peek('SalesQty', -5),0), 
     if(Item_ID=Peek('Item_ID',-6),Peek('SalesQty', -6),0), if(Item_ID=Peek('Item_ID',-7),Peek('SalesQty', -7),0), 
     if(Item_ID=Peek('Item_ID',-8),Peek('SalesQty', -8),0), if(Item_ID=Peek('Item_ID',-9),Peek('SalesQty', -9),0), 
     if(Item_ID=Peek('Item_ID',-10),Peek('SalesQty', -10),0), if(Item_ID=Peek('Item_ID',-11),Peek('SalesQty', -11))) /
     
     RangeSum(if(Item_ID<>Previous(Item_ID),1,0), 
     if(Item_ID=Peek('Item_ID'),1,0), if(Item_ID=Peek('Item_ID',-1),1,0), 
     if(Item_ID=Peek('Item_ID',-2),1,0), if(Item_ID=Peek('Item_ID',-3),1,0), 
     if(Item_ID=Peek('Item_ID',-4),1,0), if(Item_ID=Peek('Item_ID',-5),1,0), 
     if(Item_ID=Peek('Item_ID',-6),1,0), if(Item_ID=Peek('Item_ID',-7),1,0), 
     if(Item_ID=Peek('Item_ID',-8),1,0), if(Item_ID=Peek('Item_ID',-9),1,0), 
     if(Item_ID=Peek('Item_ID',-10),1,0), if(Item_ID=Peek('Item_ID',-11),1,0)) as Avg_12_Month_Sales
Resident Data
Order by Item_ID,"Reporting Date";

Drop Table Data;

[ksharpes]

The below part doesn't work, 

Data:
LOAD
    Item_ID,
    "Sum([Stock Qty])" as StockQty,
    "Sum([Sales Qty (All)])" as SalesQty,
    "Sum([Sales Qty (Ext)])" as SalesQtyExt,
    "Reporting Date"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

I've changed it to the below but still no joy.

Data:
LOAD
Item_ID,
Sum([Stock Qty]) as StockQty,
Sum([Sales Qty (All)]) as SalesQty,
Sum([Sales Qty (Ext)]) as SalesQtyExt,
"Reporting Date"
FROM [lib://Qlik Data Folder/Data_Values_2.0.QVD](qvd);

[Kushal_Chawda · Accepted solution]

Hi , Change the Field names according to your field names in QVD. for eg. Highlighted are the field names in your QVD

Data:
LOAD
ItemID as Item_ID,
Stock_qty as StockQty,
Sales_Qty as SalesQty,
Sales_Qty_Ext as SalesQtyExt,
Report_Date as "Reporting Date"
FROM [lib://Qlik Data Folder/Data_Values_2.0.QVD](qvd);