Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ksharpes
Creator
Creator

Sum Data based on previous records

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_IDSum([Stock Qty])Sum([Sales Qty (All)])Sum([Sales Qty (Ext)])Reporting DateCoverage
Item 190101031/10/20189
Item 1100101030/09/201810
Item 1110101031/08/201811
Item 1120101031/07/201812
Item 1130101030/06/201813
Item 1140101031/05/201814
Item 1150101030/04/201815
Item 1160101031/03/201816
Item 1170101028/02/201817
Item 1180101031/01/201818
Item 1190101031/12/201719
Item 1200101030/11/201720
Item 1210101031/10/201721
Item 1220101030/09/201722
Item 1230101031/08/201723
Item 1240101031/07/201724
Item 1250101030/06/201725
Item 1260101031/05/201726
Item 1270101030/04/201727
Item 1280101031/03/201728
Item 1290101028/02/201729
Item 1300101031/01/201730
Item 290101031/10/20189
Item 2100101030/09/201810
Item 2110101031/08/201811
Item 2120101031/07/201812
Item 2130101030/06/201813
Item 2140101031/05/201814
Item 2150101030/04/201815
Item 2160101031/03/201816
Item 2170101028/02/201817
Item 2180101031/01/201818
Item 2190101031/12/201719
Item 2200101030/11/201720
Item 2210101031/10/201721
Item 2220101030/09/201722
Item 2230101031/08/201723
Item 2240101031/07/201724
Item 2250101030/06/201725
Item 2260101031/05/201726
Item 2270101030/04/201727
Item 2280101031/03/201728
Item 2290101028/02/201729
Item 2300101031/01/201730

 

2 Solutions

Accepted Solutions
Kush
MVP
MVP

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;

View solution in original post

Kush
MVP
MVP

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);

View solution in original post

9 Replies
Kush
MVP
MVP

can you give one example?

ksharpes
Creator
Creator
Author

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

Kush
MVP
MVP

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

ksharpes
Creator
Creator
Author

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

Kush
MVP
MVP

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
Creator
Creator
Author

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

Kush
MVP
MVP

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;

View solution in original post

ksharpes
Creator
Creator
Author

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);

Kush
MVP
MVP

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);

View solution in original post