Calculate sales per week via the stock-level

[krippner]

Hello together, 

i would like to calculate the sold pieces per week via the stock level. 

Here you can see my example:

How can i integrate the delivery and how can i present the sold pieces in a table per week? 

In excel i would use the following formula: (Stock from Sunday last week) +( Delivery in current week) - (Remaining Stock on Sunday)

How can i replicate it in qlik?

Many thanks, 

Sven 

[dplr-rn]

Easiest way maybe to create a sales column on load

sales = previous days stock - todays stock

on load script it would look something like below

previous(sales)-sales as Stock

[Rohan]

Hey, It's a bit trickier to explain over text but i'll try my best. 
Firstly lets break your formula in 3 parts : Stock on Previous Sunday, Delivery in current week & Stock on Current week's Sunday.

You will require 2 mapping tables to get the Date wise Stock & Week-wise Delivery.

Stock_map :

mapping load 

Date,

Stocks;

Load Date,sum(Stock) as Stocks From [...] group by Date; 

Delivery_map :

mapping Load 

WeekDate,

WeeklyDelivery;

Load

weekstart(Date,0,0) as WeekDate, Sum(Delivery) as WeeklyDelivery From [...] group by weekstart(Date,0,0); 

Now you need to map the 3 parts of your formula using these 2 tables :

Temp :

Load Distinct

weekstart(Date,0,0) as DateA

Resident MainData;

Final_Data :

Load 

DateA,

Week(DateA) as WeekA,

Applymap('Stock_map ',date(num(Weekstart(DateA,0,0)-1)),null()) as Prev_Sunday_Stock,

Appplymap('Stock_map',date(num(Weekstart(DateA,1,0)-1)),null()) as Current_Sunday_Stock,

Applymap('Delivery_Map',DateA,null()) as Weekly_Delivery

Resident Temp;

 drop table Temp;

Now You can use WeekA As Dimension in the Table &

use ((Prev_Sunday_Stock+Weekly_Delivery)-Current_Sunday_Stock) as a measure.

Try this out & revert if there are any doubts.

Regards,

Rohan.

[Kushal_Chawda]

Would you be able to share sample data?

[krippner]

Yes for sure. Thank you.

[dplr-rn]

Use below script basically i am checking

Sales = stock+delivery from day before - todays end stock 

load *
	,week(Date) as Week
    ,if(previous(Stock) + +alt(Previous(Delivery),0) >0, // to handle 0s
(previous(Stock)+alt(Previous(Delivery),0))-Stock,0) as Sales

    ;
LOAD
    "Date",
    Stock,
    Delivery
FROM [lib://Downloads/Sample.xlsx]
(ooxml, embedded labels, table is Table2);

Outputs

[Kushal_Chawda]

try below

Data:
LOAD 
     Day, 
     Date, 
     Dual('W'&Week(Date,0),Week(Date,0)) as Week,
     if(floor(Date)=floor(WeekEnd(Date,0,0)),1,0) as Weekend_Flag,
     Stock, 
     Delivery
FROM
[Sample.xlsx]
(ooxml, embedded labels, table is Table2);

Left Join(Data)
LOAD date(Date+1) as Date,
     Stock as Previous_week_sunday_stock
Resident Data
where Weekend_Flag=1;

Now you can create graph with Dimension Week & below expression

=rangesum(sum(Previous_week_sunday_stock),sum(Delivery),-sum({<Weekend_Flag={1}>}Stock))