Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Hani
Partner - Contributor II
Partner - Contributor II

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
Taoufiq_Zarra

@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") 😉
Taoufiq_Zarra

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") 😉
Hani
Partner - Contributor II
Partner - Contributor II
Author

 

@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,

 

Taoufiq_Zarra

@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") 😉
Hani
Partner - Contributor II
Partner - Contributor II
Author

@Taoufiq_Zarra 

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

Taoufiq_Zarra

@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") 😉
Hani
Partner - Contributor II
Partner - Contributor II
Author

 

@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

Taoufiq_Zarra

@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") 😉
Hani
Partner - Contributor II
Partner - Contributor II
Author

@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