Announcements
cancel
Showing results for
Did you mean:
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)

• ### SaaS

9 Replies

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

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Partner - Contributor II
Author

Before expanding Month I have this Agreement for october

After expanding the month, I am expecting these results.

Regards,

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Partner - Contributor II
Author

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

@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 - Contributor II
Author

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.

@Hani  One solution:

Suppose your input data is :

In Script load you can use :

``````Data:

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

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

drop table Data;``````

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:

Regards,
Taoufiq ZARRA

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

(you can mark up to 3 "solutions") 😉
Partner - Contributor II
Author

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:

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