Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
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
Kushal_Chawda

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

Kushal_Chawda

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
Kushal_Chawda

can you give one example?

ksharpes
Creator
Creator
Author

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

Kushal_Chawda

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

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

Kushal_Chawda

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

Kushal_Chawda

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