partial sums issue

[paulwalker]

Hi Community,

I'm facing issue with partial sums, problem was have used Aggr calculated expression.

Please any one can help me.

Thanks in Advance

[sunny_talwar]

It seems that the attached dashboard has section access. Can you remove it and repost it

[paulwalker]

Please can you check now

[sunny_talwar]

I am able to open up, now can you tell us what exactly is the issue you are facing?

[bc-thebruuu · Accepted solution]

Hello

This should do the trick:

sum(Aggr(IF(Rep_Dept='Con Ops',

(SUM(AGGR(SUM(TOTAL <Month, Rep_Dept> {<Week=>} ConOpsTarget), Week, Month, Rep_Dept))

/

NetWorkDays(MonthStart(MIN(Common_Date)), MonthEnd(Max(Common_Date)),  Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')))

*

NetWorkDays(MIN(Common_Date), MAX(Common_Date), Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')),

(SUM([Charge Rate] * [Weekly Time Cost Hours]) / 5) *

NetWorkDays(SUM(DISTINCT Aggr(NODISTINCT MIN({<[Staff Name]=, Rep_Dept=>}Common_Date), Week)), SUM(DISTINCT Aggr(NODISTINCT MAX({<[Staff Name]=, Rep_Dept=>}Common_Date), Week)),

Date#('01/01/2018', 'MM/DD/YYYY'), Date#('03/30/2018', 'MM/DD/YYYY'),

Date#('04/02/2018', 'MM/DD/YYYY'), Date#('05/07/2018', 'MM/DD/YYYY'), Date#('05/28/2018', 'MM/DD/YYYY'), Date#('08/27/2018', 'MM/DD/YYYY'), Date#('12/25/2018', 'MM/DD/YYYY'), Date#('12/26/2018', 'MM/DD/YYYY')))

,Month, Rep_Dept,Week,

//Common_Date,[Charge Rate],

//[Weekly Time Cost Hours],

[Staff Name]

)

)

[paulwalker]

Thank you