Divide Month amount by weeks in a month

[Hani]

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,

[Taoufiq_Zarra]

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

[Taoufiq_Zarra]

In load Script ?

[Hani]

@Taoufiq_Zarra  Thanks for your reply!

Before expanding Month I have this Agreement for october

After expanding the month, I am expecting these results.

Regards,

[Taoufiq_Zarra]

@Hani  in load script ?

[Hani]

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

[Hani]

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

[Taoufiq_Zarra]

@Hani  One solution:

Suppose your input data is :

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:

[Hani]

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