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
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Partner
Partner

Divide Month amount by weeks in a month

Hi, I have a pivot table and columns YearMonth, week, Agreement, amount, and project.
The agreement for each project is determined for each month.
And what I want to do is when I expand month to week, I want to divide the agreement equally for each week (weekdays) in that month.

For exapmple, if the agreement for Project AAA is 100 for each month, 
In October 2020, for example, we have five weeks
 The Agreement should be divided equally into five weeks and the first week the first week(week 40) has only two working days so  should get 4.54 all the other four weeks have 5 working days , so they should get  ​​22.7.

How can I d this?

Regards,

 

Labels (2)
9 Replies
Master II
Master II

@Hani  can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Master II
Master II

In load Script ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner
Partner

 

@Taoufiq_Zarra  Thanks for your reply!

Before expanding Month I have this Agreement for october

Capture.PNG

 

After expanding the month, I am expecting these results.

week.PNG

Regards,

 

Master II
Master II

@Hani  in load script ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner
Partner

@Taoufiq_Zarra 

I'm pretty new to the Qlik Sense, and I do not know how to do it.

Master II
Master II

@Hani  can you elaborate how you get 22.7 and 4.54 ?

and if its 22.7 for 5 days how you get 4.54 for 2 days ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner
Partner

 

@Taoufiq_Zarra 

Oh, Sorry, it should be 9.09 for week 40.
If the value(Agreement) for October is 100 since we have five weeks in October 2020, the amount 100 divided by five weeks will be 9.09, 22.7, 22.7, 22.7, 22.7 when you add them together, it will be 100. We have 9.09 for the first week( week 40) because we have two working days in that week for October.

 

 

tempsnip.png

Master II
Master II

@Hani  One solution:

Suppose your input data is :

Taoufiq_Zarra_0-1605191709185.png

In Script load you can use :

Data:

load * inline [
Project,Month,Agrrement
Project AAA,oct.-20,100
];
left join
load Project,Month,'W-'&Week(MonthStart(Date#(Month,'MMM-YY')) + IterNo() - 1) as Week,if(weekday(Date(MonthStart(Date#(Month,'MMM-YY')) + IterNo() - 1))<5,1,0) as isweekend ,Date(MonthStart(Date#(Month,'MMM-YY')) + IterNo() - 1) as TempDate resident Data  
While (MonthStart(Date#(Month,'MMM-YY'))) + IterNo() -1 <=  (MonthEnd(Date#(Month,'MMM-YY')));

output:
noconcatenate

load distinct Project,Month,Agrrement,Week resident Data;
left join load Project,Month,sum(isweekend) as NbrdayTmp,Week  resident Data group by Project,Month,Week;

drop table Data;

to change load * inline [...]; by your data (ODBC,EXCEL,....)

then after load in UI you can use Pivot table with

Dimension :

Project,Month and Week (and you can pivot Month and Week)

in Measure :

=(sum(Agrrement)/sum(total<Agrrement,Month> NbrdayTmp))*NbrdayTmp

 

the output:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Partner
Partner

@Taoufiq_Zarra 

Thank you for your reply!
The Problem I am having is when I add another project  it doesn't calculate it correctly.

Agreement for a project has the same value for each month.

 

Data:

load * inline [
Project,Month,Agrrement
Project AAA,2020-10,100
Project AAA,2020-11,100
Project BBB,2020-10,200
Project BBB,2020-11,200

];
left join
load Project,Month,'W-'&Week(MonthStart(Date#(Month,'YYYY-MM')) + IterNo() - 1) as Week,if(weekday(Date(MonthStart(Date#(Month,'YYYY-MM')) + IterNo() - 1))<5,1,0) as isweekend ,Date(MonthStart(Date#(Month,'YYYY-MM')) + IterNo() - 1) as TempDate resident Data
While (MonthStart(Date#(Month,'YYYY-MM'))) + IterNo() -1 <= (MonthEnd(Date#(Month,'YYYY-MM')));

output:
noconcatenate

load distinct Project,Month,Agrrement,Week resident Data;
left join load Project,Month,sum(isweekend) as NbrdayTmp,Week resident Data group by Project,Month,Week;

drop table Data;

Capture.PNG

Capture.PNG