# New to Qlik Sense

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

Announcements
Qlik Cloud Maintenance is scheduled between March 27-30. Visit Qlik Cloud Status page for more details.
cancel
Showing results for
Did you mean:
Contributor

## Calculate sales per week via the stock-level

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

6 Replies
Partner - Master III

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

Creator

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 :

Date,

Stocks;

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

Delivery_map :

WeekDate,

WeeklyDelivery;

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 :

weekstart(Date,0,0) as DateA

Resident MainData;

Final_Data :

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.

Would you be able to share sample data?

Contributor
Author

Yes for sure. Thank you.

Partner - Master III

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

;
"Date",
Stock,
Delivery
(ooxml, embedded labels, table is Table2);``````

Outputs

try below

``````Data:
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)
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))``

Tags
Community Browser